Laravel query buillder limit only the rows from a table even if in a join those rows have to appear more than once

40
November 16, 2019, at 5:30 PM

I have 2 tables:

Foo
| id | car  |
_____________
| 1  |  A   |
| 2  |  B   |
| 3  |  C   |
Bar 
| car_id | color |
__________________
|   1    | red   |
|   1    | green |
|   1    | blue  | 
|   2    | pink  |
|   2    | red   |
|   3    | blue  |

I want to limit my join to 2 rows from Foo table(car A and B). For that i tried using this query:

$cars = \DB::table('Foo')->join('Bar', 'Bar.car_id', '=', 'Foo.id')
        ->select('Foo.car', 'Bar.color')
        ->take(2)
        ->get();

The problem with this query is that i'm getting only the first two results for car A since it appears twice in Bar table:

Result
[1] A, red
[2] A, green

But i want to get something like this:

Result
[1] A, red
[2] A, green
[3] A, blue
[4] B, pink
[5] B, red

How can i do that by using the query builder from laravel? (not Eloquent)

Answer 1

Something like:

$cars = \DB::table('Foo')->take(2)->join('Bar', 'Bar.car_id', '=', 'Foo.id')
        ->select('Foo.car', 'Bar.color')
        ->get();

doesn't work?

READ ALSO
loading data into combo object with sql query result in Java

loading data into combo object with sql query result in Java

I would like to load a combo object with a result of sql queryI do not know how to convert List into String[]

56
MySQL - Checking nonexistent records

MySQL - Checking nonexistent records

I'm currently trying to check for existing rows in mysql

55