Wednesday 2 May 2018

Complex Laravel Eloquent Example



select   n0y_t_item_description.s_title,
n0y_t_item_description.s_slug as item_slug,
n0y_t_item.d_price         as price,
n0y_t_category_description.s_name,
n0y_t_category_description.s_slug as category_slug,
n0y_t_item_resource.pk_i_id as path_id,
n0y_t_item_resource.s_path as image_path,
n0y_t_item_resource.s_extension as path_ext,
parent_slug from `n0y_t_item` inner join `n0y_t_item_description` on `n0y_t_item_description`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join `n0y_t_category` on `n0y_t_category`.`pk_i_id` = `n0y_t_item`.`fk_i_category_id` inner join `n0y_t_category_description` on `n0y_t_category`.`pk_i_id` = `n0y_t_category_description`.`fk_i_category_id` inner join `n0y_t_item_resource` on `n0y_t_item_resource`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join (select pk_i_id, s_slug as parent_slug
from n0y_t_category join n0y_t_category_description on n0y_t_category.pk_i_id = n0y_t_category_description.fk_i_category_id
) as n0y_pCat on `n0y_pCat`.`pk_i_id` = `n0y_t_category`.`fk_i_parent_id` where (`b_active` = '1' and `i_main` = '1') order by RAND() limit 7
         
   
   
   
        $sql = '  xxt_item_description.s_title, 
                  xxt_item_description.s_slug as item_slug, 
                  xxt_item.d_price         as price,
                  xxt_category_description.s_name, 
                  xxt_category_description.s_slug as category_slug,
                  xxt_item_resource.pk_i_id as path_id,
                  xxt_item_resource.s_path as image_path,
                  xxt_item_resource.s_extension as path_ext,
                  parent_slug';

        $sql = str_replace('xx', DB::getTablePrefix(), $sql);


        $joinQry =    "(select pk_i_id, s_slug as parent_slug
                   from xxt_category join xxt_category_description on xxt_category.pk_i_id = xxt_category_description.fk_i_category_id
            ) as xxpCat";

        $joinQry = str_replace('xx', DB::getTablePrefix(), $joinQry);

        $items = DB::table('t_item')
            ->select(DB::raw(
                $sql
            ))
            ->join('t_item_description', 't_item_description.fk_i_item_id', 't_item.pk_i_id')
            ->join('t_category', 't_category.pk_i_id', 't_item.fk_i_category_id')
            ->join('t_category_description', 't_category.pk_i_id', 't_category_description.fk_i_category_id')
            ->join('t_item_resource', 't_item_resource.fk_i_item_id', 't_item.pk_i_id')
            ->where(function ($q) use ($tenDaysAgo, $currentDate) {
                $q->where('b_active', 1)
                   ->where('i_main',1);
//                    ->whereDate('dt_expiration', '>', date($currentDate->toDateTimeString()))
//                    ->whereBetween('dt_pub_date', array(date($tenDaysAgo->toDateTimeString()), date($currentDate->toDateTimeString())));
            })
            ->join(DB::raw($joinQry), function ($join) {
                $join->on("pCat.pk_i_id", "=", "t_category.fk_i_parent_id");
            })

            ->orderByRaw('RAND()')
            // ->order('dt_pub_date','desc')
            ->take(7)
            ->get();



ORIGINAL SQL WHERE EXISTS


SELECT *
FROM `items`
WHERE EXISTS
    (SELECT `items_city`.`id`
     FROM `items_city`
     WHERE items_city.item_id = items.id)
     
Laravel Eloquent Query with Update


  DB::table('contributions')
            ->whereStatusId(ContributionStatus::RECONCILED)
            ->whereNull('submission_id')
            ->whereExists(function ($query) {
                $query->select("declarations.id")
                    ->from('declarations')
                    ->whereRaw('declarations.id = contributions.declaration_id');
            })
            ->update([
                'submission_id' => $submission->id,
            ]);

Share:

Popular Posts

Recent Posts

Pages

Powered by Blogger.

About Me

My photo
For the past 10 years, I've been playing with codes using PHP, Java, Rails. I do this for a living and love new things to learn and the challenges that comes with it. Besides programming I love spending time with friends and family and can often be found together catching the latest movie or planning a trip to someplace I've never been before.