CODEWITHSUNDEEP
phpmysqllaraveleloquent
benJ
benJ

Reputation: 2590

Eloquent - join clause with string value rather than column heading

I have a question regarding join clauses in Eloquent, and whether you can join on a string value rather than a table column.

I have the code below querying a nested set joining parent/child records in a table 'destinations' via a table 'taxonomy'.

The second $join statement in the closure is the one causing an issue; Eloquent assumes this is a column, when I would actually just like to join on t1.parent_type = 'Destination' - ie, t1.parent_type should = a string value, Destination. 

$result = DB::connection()
    ->table('destinations AS d1')
    ->select(array('d1.title AS level1', 'd2.title AS level2'))
    ->leftJoin('taxonomy AS t1', function($join) {
        $join->on('t1.parent_id', '=', 'd1.id');
        $join->on('t1.parent_type', '=', 'Destination');
    })
    ->leftJoin('destinations AS d2', 'd2.id', '=', 't1.child_id')
    ->where('d1.slug', '=', $slug)
    ->get();

Is it possible to force Eloquent to do this? I've tried replacing 'Destination' with DB::raw('Destination') but this does not work either.

Thanking you kindly.

Upvotes: 35

Views: 19217

Answers (2)

mukund
mukund

Reputation: 2423

Another best way to achieve same is :

$result = DB::connection()
    ->table('destinations AS d1')
    ->select(array('d1.title AS level1', 'd2.title AS level2'))
    ->leftJoin('taxonomy AS t1', function($join) {
        $join->on('t1.parent_id', '=', 'd1.id');
        $join->where('t1.parent_type', '=', 'Destination');
    })
    ->leftJoin('destinations AS d2', 'd2.id', '=', 't1.child_id')
    ->where('d1.slug', '=', $slug)
    ->get();

Replace your on with where

Upvotes: 46

Krishna Raj
Krishna Raj

Reputation: 866

try using DB::raw("'Destination'")

Upvotes: 10

Related Questions