0

Hello guys I am working on a project build using laravel 5.6, I want to filter results in search, based on conditions that applies to different columns from both the tables.

I have two tables one is Adverts and other is Vehicle_Specifications

Adverts has columns:

[id, user_id, category_id, status_id, promotion_id,title, description, state, city, cost, slug]

Adverts table is also related to Ad_Images table which holds all the images of the adverts according to their ids.

Vehicle_Specifications has columns:

[id, advert_id, brand, model, year, body_type, doors, color, milage, fuel_type]

Scenario: Now I want to filter data from adverts table based on status_id, state, city then get those adverts which has filters matching in vehicle_specifications table like brand = honda, model = civic etc. and also paginate the result.

Query that I have tried:

$filtered_vehicles = Ad::with('vehiclespecification')->where([['state', $state]])->whereHas('vehiclespecification', function ($query) {
                $query->where('brand', 'like',$_GET['make'].'%');
            })->paginate(50);

the above query does give me some results but it is not perfect.

my query log:

^ array:3 [▼
  0 => array:3 [▼
    "query" => "select count(*) as aggregate from `ads` where (`state` = ?) and exists (select * from `vehiclespecifications` where `ads`.`id` = `vehiclespecifications`.`ad_id` ▶"
    "bindings" => array:2 [▼
      0 => "Punjab"
      1 => "Honda%"
    ]
    "time" => 95.87
  ]
  1 => array:3 [▼
    "query" => "select * from `ads` where (`state` = ?) and exists (select * from `vehiclespecifications` where `ads`.`id` = `vehiclespecifications`.`ad_id` and `brand` like ?) ▶"
    "bindings" => array:2 [▼
      0 => "Punjab"
      1 => "Honda%"
    ]
    "time" => 79.32
  ]
  2 => array:3 [▼
    "query" => "select * from `vehiclespecifications` where `vehiclespecifications`.`ad_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ▶"
    "bindings" => array:28 [▼
      0 => 369
      1 => 384
      2 => 391
      3 => 396
      4 => 517
      5 => 755
      6 => 964
      7 => 997
      8 => 998
      9 => 1029
      10 => 1050
      11 => 1054
      12 => 1060
      13 => 1078
      14 => 1127
      15 => 1148
      16 => 1155
      17 => 1279
      18 => 1322
      19 => 1387
      20 => 1465
      21 => 1467
      22 => 1470
      23 => 1473
      24 => 1474
      25 => 1475
      26 => 1476
      27 => 1478
    ]
    "time" => 0.87
  ]
]

Any help would be much appreciated.

Thank you

AK Devs
  • 34
  • 7
  • *but it is not perfect* Can you explain in detail, are you getting wrong results or any other issue – M Khalid Junaid Aug 31 '20 at 11:25
  • Salam Khalid! No i am getting right results but it shows 28 results in search when i use the above query and when i check the data base it has some 50 results. – AK Devs Aug 31 '20 at 11:30
  • 1
    Can you print your [generated sql query](https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string) and run in database , or analyze your query may be its not constructed like you want it to be – M Khalid Junaid Aug 31 '20 at 11:41
  • @MKhalidJunaid this is please check the below answer. – AK Devs Aug 31 '20 at 16:22
  • @MKhalidJunaid I have added my query log to the question asked. – AK Devs Aug 31 '20 at 17:50
  • I think you should post the expected result, check what data is stored in the db, etc. – Keyu Gan Sep 01 '20 at 02:15
  • @KeyuGan I have already checked data in db it exists and the count for expected data is 56 whereas i am getting 28 results, also the expected results should have title, description, cost, images based on the filters mentioned in the questions. – AK Devs Sep 01 '20 at 02:39
  • 1
    or maybe you just missed where('column', 'like', "%$value_to_search%") – Alzafan Christian Sep 01 '20 at 05:52
  • @AlzafanChristian I have tried that also but same result – AK Devs Sep 01 '20 at 14:34

0 Answers0