-1

I have this code:

$businesses = Business::filter($filters)
    ->select([
        'businesses.*',
        'subscriptions.current_period_end',
        'subscriptions.business_id',
        'cities.name AS city_name',
        'counties.name AS county_name',
        'business_category.*',
        'categories.name AS category_name'
    ])
    ->distinct()
    ->leftJoin('subscriptions', 'subscriptions.business_id', '=', 'businesses.id')
    ->leftJoin('cities', 'cities.id', '=', 'businesses.city_id')
    ->leftJoin('counties', 'counties.id', '=', 'cities.county_id')
    ->leftJoin('business_category', 'business_category.business_id', '=', 'businesses.id')
    ->leftJoin('categories', 'business_category.category_id', '=','categories.id')
    ->orderBy($sortBy, $sortDir)
    ->paginate(10);

Problem is that it creates multiple results with the same data ex: Result with id 44 is shown in pagination 4 times.

I have tried using distinct with no avail, if I try using group by it will throw an error regarding duplicate keys ex: Column already exists: 1060 Duplicate column name 'business_id' Thank you in advance for the responses.

matiaslauriti
  • 4,495
  • 4
  • 29
  • 37
Iulian Oprea
  • 1
  • 1
  • Does this answer your question? [SQL duplicate rows with multiple left joins](https://stackoverflow.com/questions/46279081/sql-duplicate-rows-with-multiple-left-joins). get rid of left joins where they are not needed – Ol D. Castor May 31 '22 at 18:59
  • Why are you doing that instead of using relationships? – matiaslauriti May 31 '22 at 19:09
  • I need to use sorting on appended attributes and I have too many records to use sortBy – Iulian Oprea May 31 '22 at 19:16
  • @IulianOprea is challenging to help you without the database tables restructure but your problem with the duplicate column is because the `businesses.*` is bringing the `bussiness_id` column and is duplicated with `subscriptions.business_id`, my recommendation is never to use `*` in your database queries from the code because that make performance problems and also confusions like that column naming. – Jose Lora May 31 '22 at 21:11
  • [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) – philipxy Jun 01 '22 at 04:34
  • 1
    Thanks for the answers everyone, I am quite a noob. It is my first job, @JoseLora 's answer made me rethink it and truly the issue stems from the subscriptions. – Iulian Oprea Jun 01 '22 at 10:24

0 Answers0