Laravel：在查询构建器上背靠背使用first（）时出错

Question

I have the following code in my laravel project

$config = DB::table('custom_config')->where('item_id', 5);
$cost = [
    'car_service_fee' => $config->where('managed_by', 1)->first()->service_fee,
    'bike_service_fee' => $config->where('managed_by', 2)->first()->service_fee
];

My custom_config table is as of below.

+---------+------------+-------------+
| item_id | managed_by | service_fee |
|---------+------------+-------------|
|    5    |       1    |    8.5      |
|---------+------------+-------------|
|    5    |       2    |    2.0      |
+---------+------------+-------------+

my car_service_fee is fetching the result of 8.5

but my bike_service_fee is returning null on first()

The same code works if it is just like given below,

$cost = [
    'car_service_fee' => DB::table('custom_config')->where('item_id', 5)->where('managed_by', 1)->first()->service_fee,
    'bike_service_fee' => DB::table('custom_config')->where('item_id', 5)->where('managed_by', 2)->first()->service_fee
];

Is there any problem on back to back first() method used on a query builder that is stored in a variable or something in laravel?

Thank you

Answer 1

$config is a Query Builder object. The majority of calls you are making on this object are 'building' a query. The object keeps all these where conditions internally. When a method to execute the query is called it will compile the query, execute it and return a result [calling first or get or ...]. The builder itself still exists as a builder and can continue to be built upon or the query can be executed again, etc.

In your case you are adding more where conditions to this single query object, $config, every time you call where on it.

You can see this behavior at any time by calling toSql on a builder to see what the generated query would look like.

You can avoid this by creating a new builder object or cloning $config so you can have 2 separate queries being built.

Example:

$config = DB::table('custom_config')->where('item_id', 5);
$config2 = clone $config;

$cost = [
     'car_service_fee' => $config->where('managed_by', 1)->first()->service_fee,
     'bike_service_fee' => $config2->where('managed_by', 2)->first()->service_fee
];

$config and $config2 both have the first where condition.

You could just clone them inline as well, if you don't need these builders after the fact:

'car_service_fee' => (clone $config)->where(...)->first()->...,
'bike_service_fee' => (clone $config)->where(...)->first()->...,