laravel中的Sql查询

Question

i have two tables, products table and its photos table. I want to query products with one of their photo. my below sql query give me the result but in laravel i don't know how to use functions to take same result.

  select products.*, pphotos.name as pname from products 
         INNER JOIN pphotos on products.id = pphotos.product_id
         GROUP BY products.id

Answer 1

You need to make use of the query builder https://laravel.com/docs/5.8/queries

$products = DB::table('products')
    ->select('products.*', 'pphotos.name as pname')
    ->join('pphotos', 'products.id', '=', 'pphotos.product_id')
    ->groupBy('products.id')
    ->get();

Edit

To work around the issue where an item in your select list is not functionally dependent, you can modify the query like so:

$products = DB::table('products')
    ->selectRaw('products.*, ANY_VALUE(pphotos.name) as pname')
    ->join('pphotos', 'products.id', '=', 'pphotos.product_id')
    ->groupBy('products.id')
    ->get();

or you can simply disable strict mode in your config/db.php

'mysql' => [
    ...
    'strict' => false,
]

Disclaimer

However, note that if the pphotos.name within the same group differ in value, it will be unpredictable which pphotos.name mysql will return as a result.