How to execute subquery with Laravel

Question

I fetch all product online store by this query and i want use it in Laravel 5.3

Because it's a nested query, I could not understand it well.

How can I use this query by Laravel functions:

SELECT 
        pe.entity_id AS eid,
        tbl.attribute_id AS attr_id,
        tbl.attribute_code AS attr_code,
        tbl.`value`
FROM tdshop_product_category AS pc 
INNER JOIN tdshop_product_entity  AS pe ON ( pc.product_id = pe.entity_id AND pe.deleted_at IS NULL)
LEFT JOIN (
        SELECT 
            att.attribute_id,
            att.attribute_code,
          CASE att.backend_type
           WHEN 'varchar' THEN pe_varchar.`value`
           WHEN 'int' THEN pe_int.`value`
           WHEN 'text' THEN pe_text.`value`
           WHEN 'decimal' THEN pe_decimal.`value`
           WHEN 'datetime' THEN pe_datetime.`value`
           ELSE att.backend_type
          END AS `value`,
          CASE att.backend_type
           WHEN 'varchar' THEN pe_varchar.`entity_id`
           WHEN 'int' THEN pe_int.`entity_id`
           WHEN 'text' THEN pe_text.`entity_id`
           WHEN 'decimal' THEN pe_decimal.`entity_id`
           WHEN 'datetime' THEN pe_datetime.`entity_id`
           ELSE att.backend_type
          END AS `entity_id`
        FROM tdshop_entity_attribute AS ea 
        LEFT JOIN tdshop_attribute AS att ON (att.attribute_id = ea.attribute_id)
        LEFT JOIN tdshop_product_entity_varchar AS pe_varchar ON (pe_varchar.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_int AS pe_int ON (pe_int.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_text AS pe_text ON (pe_text.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_decimal AS pe_decimal ON (pe_decimal.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_datetime AS pe_datetime ON (pe_datetime.attribute_id = att.attribute_id)
) AS tbl ON ( tbl.entity_id = pe.entity_id)
GROUP BY pe.entity_id,tbl.attribute_id
ORDER BY eid DESC

Thanks in advance.

Answer 1

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:

DB::table('combinations')
->select('combinations.id', DB::raw('GROUP_CONCAT(diseases.name ORDER BY diseases.id) AS DieasesName'))
->join('diseases', function($join) {
    $join->on(DB::raw('FIND_IN_SET(diseases.id, combinations.diseases_id)'), '>', 0);
})

OR if you want to use DB::select be careful SQL injection .. use this as following sample :

$results = DB::select('select * from users where id = :id', ['id' => 1]);

or

$results = DB::select('select * from users where id = ?', [1]);

in your code :

$sql = "
SELECT 
        pe.entity_id AS eid,
        tbl.attribute_id AS attr_id,
        tbl.attribute_code AS attr_code,
        tbl.`value`
FROM tdshop_product_category AS pc 
INNER JOIN tdshop_product_entity  AS pe ON ( pc.product_id = pe.entity_id AND pe.deleted_at IS NULL)
LEFT JOIN (
        SELECT 
            att.attribute_id,
            att.attribute_code,
          CASE att.backend_type
           WHEN 'varchar' THEN pe_varchar.`value`
           WHEN 'int' THEN pe_int.`value`
           WHEN 'text' THEN pe_text.`value`
           WHEN 'decimal' THEN pe_decimal.`value`
           WHEN 'datetime' THEN pe_datetime.`value`
           ELSE att.backend_type
          END AS `value`,
          CASE att.backend_type
           WHEN 'varchar' THEN pe_varchar.`entity_id`
           WHEN 'int' THEN pe_int.`entity_id`
           WHEN 'text' THEN pe_text.`entity_id`
           WHEN 'decimal' THEN pe_decimal.`entity_id`
           WHEN 'datetime' THEN pe_datetime.`entity_id`
           ELSE att.backend_type
          END AS `entity_id`
        FROM tdshop_entity_attribute AS ea 
        LEFT JOIN tdshop_attribute AS att ON (att.attribute_id = ea.attribute_id)
        LEFT JOIN tdshop_product_entity_varchar AS pe_varchar ON (pe_varchar.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_int AS pe_int ON (pe_int.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_text AS pe_text ON (pe_text.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_decimal AS pe_decimal ON (pe_decimal.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_datetime AS pe_datetime ON (pe_datetime.attribute_id = att.attribute_id)
) AS tbl ON ( tbl.entity_id = pe.entity_id)
GROUP BY pe.entity_id,tbl.attribute_id
ORDER BY eid DESC
";
$results = DB::select($sql);

Answer 2

Can not test it, but it will be somehow like

$users = DB::table('tdshop_product_category')
            ->join('tdshop_product_entity', 'pc.product_id' = 'pe.entity_id', 'pe.deleted_at' = NULL)
            ->leftJoin(DB::raw("SELECT 
            att.attribute_id,
            att.attribute_code,
          CASE att.backend_type
           WHEN `varchar` THEN pe_varchar.`value`
           WHEN 'int' THEN pe_int.`value`
           WHEN 'text' THEN pe_text.`value`
           WHEN 'decimal' THEN pe_decimal.`value`
           WHEN 'datetime' THEN pe_datetime.`value`
           ELSE att.backend_type
          END AS `value`,
          CASE att.backend_type
           WHEN 'varchar' THEN pe_varchar.`entity_id`
           WHEN 'int' THEN pe_int.`entity_id`
           WHEN 'text' THEN pe_text.`entity_id`
           WHEN 'decimal' THEN pe_decimal.`entity_id`
           WHEN 'datetime' THEN pe_datetime.`entity_id`
           ELSE att.backend_type
          END AS `entity_id`
        FROM tdshop_entity_attribute AS ea 
        LEFT JOIN tdshop_attribute AS att ON (att.attribute_id = ea.attribute_id)
        LEFT JOIN tdshop_product_entity_varchar AS pe_varchar ON (pe_varchar.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_int AS pe_int ON (pe_int.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_text AS pe_text ON (pe_text.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_decimal AS pe_decimal ON (pe_decimal.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_datetime AS pe_datetime ON (pe_datetime.attribute_id = att.attribute_id)
) tbl"), function($join){
    $join->on('tbl.entity_id', '=', 'pe.entity_id')
})->groupBy('pe.entity_id,tbl.attribute_id')->orderBy('eid', 'DESC')->get();

or may need a little bit modification.

Answer 3

If you have a complicated query like that, I suggest you use DB::select method.

DB::select("SELECT 
    pe.entity_id AS eid,
        tbl.attribute_id AS attr_id,
        tbl.attribute_code AS attr_code,
        tbl.`value`
    FROM tdshop_product_category AS pc 
    INNER JOIN tdshop_product_entity  AS pe ON ( pc.product_id = pe.entity_id AND pe.deleted_at IS NULL)
    LEFT JOIN (
        SELECT 
                att.attribute_id,
                att.attribute_code,
            CASE att.backend_type
                WHEN 'varchar' THEN pe_varchar.`value`
                WHEN 'int' THEN pe_int.`value`
                WHEN 'text' THEN pe_text.`value`
                WHEN 'decimal' THEN pe_decimal.`value`
                WHEN 'datetime' THEN pe_datetime.`value`
                ELSE att.backend_type
            END AS `value`,
            CASE att.backend_type
                WHEN 'varchar' THEN pe_varchar.`entity_id`
                WHEN 'int' THEN pe_int.`entity_id`
                WHEN 'text' THEN pe_text.`entity_id`
                WHEN 'decimal' THEN pe_decimal.`entity_id`
                WHEN 'datetime' THEN pe_datetime.`entity_id`
                ELSE att.backend_type
            END AS `entity_id`
        FROM tdshop_entity_attribute AS ea 
        LEFT JOIN tdshop_attribute AS att ON (att.attribute_id = ea.attribute_id)
        LEFT JOIN tdshop_product_entity_varchar AS pe_varchar ON (pe_varchar.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_int AS pe_int ON (pe_int.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_text AS pe_text ON (pe_text.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_decimal AS pe_decimal ON (pe_decimal.attribute_id = att.attribute_id)
        LEFT JOIN tdshop_product_entity_datetime AS pe_datetime ON (pe_datetime.attribute_id = att.attribute_id)
    ) AS tbl ON ( tbl.entity_id = pe.entity_id)
    GROUP BY pe.entity_id,tbl.attribute_id
    ORDER BY eid DESC");