Laravel 4.2 Eloquent using lists() with a join query

Mark Kendall

I have a query that makes use of multiple joins:

public function scopePurchased($query, $userId)
{

    return $query
                ->join('products','characters.id','=','products.productable_id')
                ->join('bundle_product','bundle_product.product_id','=','products.id')
                ->join('bundles','bundles.id','=','bundle_product.bundle_id')
                ->join('purchases','purchases.bundle_id','=','bundles.id')
                ->join('users','purchases.user_id','=','users.id')
                ->whereNull('purchases.deleted_at')
                ->where('purchases.refunded', false)
                ->where('products.productable_type', '=', get_class($this))
                ->where('users.id','=',$userId)
                ->groupBy('characters.id')
                ->orderBy('characters.title', 'ASC');

}

And I want to retrieve an array of ID's from this query to use in another scope so:

$query->purchased($userID)->lists('id')

My initial thought was to use lists('id') which complained about an ambiguous query on the ID.

Column 'id' in field list is ambiguous 
(
SQL: select `id` from `characters` 
inner join `products` on `characters`.`id` = `products`.`productable_id` 
inner join `bundle_product` on `bundle_product`.`product_id` = `products`.`id` 
inner join `bundles` on `bundles`.`id` = `bundle_product`.`bundle_id` 
inner join `purchases` on `purchases`.`bundle_id` = `bundles`.`id` 
inner join `users` on `purchases`.`user_id` = `users`.`id` 
where `characters`.`deleted_at` is null 
and `purchases`.`deleted_at` is null 
and `purchases`.`refunded` = 0 
and `products`.`productable_type` = Character and `users`.`id` = 1 
group by `characters`.`id` 
order by `characters`.`title` asc
)

Makes sense, fair enough so I changed the lists to

$query->purchased($userID)->lists('characters.id')

Thinking that naming the table and column should fix it but finding that the lists function drops the 'character.' part and so having the same error.

It appear that lists may not use a dot notation, bring me to my question... Can I escape the dot notation or is there another way to get the list of ID's as an array?

Many thanks

Bogdan

You can alias the column name before using lists:

$query->purchased($userID)->select('characters.id as _id')->lists('_id');

This will avoid any column name conflicts.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Laravel join query using Laravel eloquent

分類Dev

Laravel join table using eloquent

分類Dev

Laravel Using ->lists() on Non-Eloquent Models

分類Dev

Laravel query multiple tables using eloquent

分類Dev

Laravel eloquent join in ajax

分類Dev

Join in Laravel's Eloquent

分類Dev

Laravel Eloquent join vs with

分類Dev

Laravel 4 over 2 Eloquent with scope

分類Dev

Eloquent query using join to retrieve pivot table and related data

分類Dev

Laravel Eloquent orWhere Query

分類Dev

Laravel eloquent query improvement

分類Dev

Laravel: Alias not correctly written when using Haversine formula on Eloquent query

分類Dev

Laravel 4 eloquent WHERE with AND and OR?

分類Dev

Laravel Eloquent query with optional parameters

分類Dev

Laravel Eloquent query behaving strangely

分類Dev

Raw query to Eloquent. laravel

分類Dev

Laravel 4.2 Eloquent Dynamic Query

分類Dev

Laravel query join on not null

分類Dev

Laravel 4 Eloquent DynamicWhere句

分類Dev

Laravel Eloquent:with()とjoin()のJSON出力

分類Dev

Laravel Eloquent LEFT JOIN WHERE NULL

分類Dev

Laravel Eloquent Subquery inside a join with alias

分類Dev

Laravel Eloquent JOIN WHERE id is null

分類Dev

Eloquent Query Builder(Laravel 4)で「SUM」で注文する方法

分類Dev

Laravel Eloquent query returning non object

分類Dev

Laravel Eloquent Repository - Query giving unexpected result?

分類Dev

Can't convert SQL query to laravel eloquent

分類Dev

Mysql Query Or Laravel Eloquent for Average of multiple column

分類Dev

Query relationship inside the relationship in Laravel Eloquent