CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-5
matt
matt

Reputation: 787

Laravel 5.0 Queries Using Query Builder with Joins

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.

Upvotes: 0

Views: 2627

Answers (1)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81147

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();

Upvotes: 3

Related Questions