Laravel 5.0 Queries Using Query Builder with Joins

Question

I have a MySQL query which I'd like to implement in Laravel 5's Query Builder format.

I have the tables Items and FaceOff. In my Laravel controller, I have referenced the namespace/Items and namespace/FaceOff models.

Query:

select i1.id as id1, i2.id as id2
from items i1 
join
     items i2 
left join
     faceoff f
     on (f.wonid = i1.id and f.lostid = i2.id and userid = '1') or
        (f.wonid = i2.id and f.lostid = i1.id and userid = '1')
where f.wonid is null and i1.id <> i2.id 
order by rand()
limit 1;

What I'm having trouble with is how to join on nested queries, and to use aliases for tables. For example, this simple query:

$items = Items::select('id as id1')

I can alias the column name, but no idea how to alias the results of the entire query.

In short, I'm trying to grab 2 random items which haven't been met in a "head to head" face off. Think of it as pairing two competitors - each competitor should only pay another competitor once. So, the query should return ID1 and ID2. Those should be different IDs and not have Won nor Lost against each other.

Question:

Can somebody help me translate this into Laravel's query builder format? I suspect I have to use the DB::raw expression.

I've tried and failed to use DB::Raw expression, which gave an error regarding the DB model not being included. I'm also hesitant to open up the system to SQL injection and regardless, struggling to work out joins.

Thanks in advance from somebody who is quite lost.

Jarek Tkaczyk · Accepted Answer · 2015-03-09 15:58:29Z

3

The only tricky part in your code is unusual join. Other parts are just simple Query\Builder methods:

// you could use model query, but it makes little sense in this case
// Items::from('items as i1')->join...
DB::table('items as i1')
  ->join( DB::raw('items as i2 left join faceoff as f'), function ($j) {
    $j->on('f.wonid', '=', 'i1.id')
        ->on('f.lostid', '=', 'i2.id')
        ->where('userid', '=', 1)
      ->orOn('f.wonid', '=', 'i2.id')
        ->on('f.lostid', '=', 'i1.id')
        ->where('userid', '=', 1);

  })->whereNull('f.wonid')
  ->where('i1.id', '<>', 'i2.id')
  ->orderByRaw('rand()')
  ->take(1)
  ->select('i1.id as id1', 'i2.id as id2')
  ->get();

answered Mar 9, 2015 at 15:58

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mercury Over a year ago

what about where .. and in the same table

Collectives™ on Stack Overflow

Laravel 5.0 Queries Using Query Builder with Joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related