CODEWITHSUNDEEP
phpmysqllaraveleloquent
Ricardo Albear
Ricardo Albear

Reputation: 516

Laravel - Two or more alias to same table in query builder

I need to replicate a SQL query in Laravel with query builder, but i don't know how to define more than one alias to he same table.

I have this SQL query working fine.

select b.url as url, b.id as id
from item a, item b
where a.type = 'big'
AND a.published = 1
AND b.parent_id = a.id
AND b.published = 1

This is the Laravel try:

$query = DB::table('item as a, item as b');
$query->where('a.type', '=', 'big');
$query->where('a.published', '=', 1);
$query->where('b.parent_id', '=', 'a.id');
$query->where('b.published', '=', 1);

I also try with:

$query = DB::table('item');
$query->fromRaw('item a, item b');
$query->where('a.type', '=', 'big');
$query->where('a.published', '=', 1);
$query->where('b.parent_id', '=', 'a.id');
$query->where('b.published', '=', 1);

I need to use the query builder because i need some conditionals in the next steps, thats why i cant use a simple 'hasMany' relationship.

Update with items table details:

Items table

Upvotes: 1

Views: 937

Answers (1)

R P
R P

Reputation: 741

#Raw Expressions

Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the DB::raw method:

https://laravel.com/docs/4.2/queries#raw-expressions

$query = DB::table(DB::raw('item as a, item as b'))
              ->where('a.type', '=', 'big')
              ->where('a.published','=',1)
              ->where('b.parent_id','=', DB::raw('a.id'))
              ->select('b.url','b.id')
              ->get();

Upvotes: 4

Related Questions