Home Mysql query in multiple row
Reply: 0

Mysql query in multiple row

user3307
1#
user3307 Published in May 27, 2018, 11:30 pm

I create a project with laravel 5.4 and mysql 5.7 database . i have two role in this project named driver and customer.

In my project a driver can select multiple skills and add multiple tags for each skill and a customer can register a order with multiple items . in order_items each item can bind to a skill and multiple tags

Notice : user skill_id have 4 part Group - Category - Subcategory - Item and each part of skill_id explain of skill level for example skill_id like G1-C2-S3 mean user selected all items of this level because user just selected three level

Another example skill_id G5-C5-S2-T7 in this skill user just selected item7 of subcategory2 of category5 of group5

Notice : user skill_id level parts is not fix

User Skills have table like below :

 skill_id      user_id
 ________________________
  G1-C2-S3       2
  G2-C4          2
  G5-C5-S2-T7    2

A user skills tags Table

 skill_id      user_id    tag
 _______________________________
  G1-C2-S3       2         nice
  G1-C2-S3       2         good
  G1-C2-S3       2          bad
  G2-C4          2          xxx
  G2-C4          2          yyy
  G5-C5-S2-T7    2          zz
  G5-C5-S2-T7    2          jjj

Order table is :

 id      title     
 ________________
  3      egergr    
  3      ergervv     
  3      ergerger    

Order items table is :

    item_id      order_id      tags
 __________________________________
  G1-C2-S3-t2       3         nice
  G2-C4-S6-t3       3          yyy
  G5-C5-S2-T7       3          zz

Notic : a user in register order can select last level of skill_id for each item so item_id is always 4 part of skill and it's fix

How can i write a query that bind a customer order to a driver with matched skills and his tags to a order items and his tags?

I wrote below code but not works

$skills = UsersSkills::with('tags')->where('user_id',$biker_id)->get();

    $revisions = function ($query) use($skills) 
    {
        foreach($skills as $skill)
        {

            $tags = ($skills[0]['tags']->toArray() !== []) ? $skills[0]['tags']->toArray() : null;
            $tags_values = (!is_null($tags)) ? array_column($tags, 'tag') : null;
            $query->where('item_id','LIKE',$skills[0]['skill_id'].'%')->whereIn('tags',$tags_values);
        }
    };

    $orders = Order::whereHas('order_items', $revisions)->where(function($query) use($maxLat,$minLat,$maxLon,$minLon,$biker_search_time,$estimation_id) {

        $query->where('done_by_expert',1);
        $query->whereBetween('destination_longitude', [$minLon, $maxLon]);
        $query->whereBetween('destination_latitude',[$minLat,$maxLat]);
        $query->where('status','=','pending');
        $query->where('type','=',NULL);
        $query->where('vehicle_type_id',$estimation_id);
        $query->whereNull('favorites');
        $query->where('created_at','>=',Carbon::now()->subMinute($biker_search_time));


    })->get();
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30727 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO