php - 如何基于表格添加自定义列 - Laravel

Question

我有一个查询，我需要在另一个表“history”的基础上添加一个列 (is_found)，如果 product_id 存在于表“history”中，则列值 (is_found) 变为 1，否则它的值保持不变0.

这是我的查询:

Product::select(
    'products.id as product_id',
    'products.name',
    'products.desc',
    'products.image',
    'products.barcode',
    'categories.name as category_name',
    'categories.id as category_id',
)
    ->leftJoin(
        'categories',
        'products.category_id',
        '=',
        'categories.id'
    )

Answer 1

试试这个:

Product::select(
    'products.id as product_id',
    'products.name',
    'products.desc',
    'products.image',
    'products.barcode',
    'categories.name as category_name',
    'categories.id as category_id',
    \DB::raw('IF(history.product_id, 1, 0) AS is_found')
)
    ->leftJoin(
        'categories',
        'products.category_id',
        '=',
        'categories.id'
    )
   ->leftJoin('history', 'products.id','=','history.product_id')

或者:

Product::select(
    'products.id as product_id',
    'products.name',
    'products.desc',
    'products.image',
    'products.barcode',
    'categories.name as category_name',
    'categories.id as category_id',
    \DB::raw('IF((select product_id from history where history.product_id = products.id), 1, 0)')
)
    ->leftJoin(
        'categories',
        'products.category_id',
        '=',
        'categories.id'
    );