Convert MySQL query to Laravel query builder code

Question

I am working with agricultural product management system. I have a question regarding a MySQL query. I would like to know how to create the same query using Laravel query builder:

SELECT
    vegitables.name, vegitables.image, vegitables.catagory,
    AVG(price_wholesale),
    SUM(CASE WHEN rank = 1 THEN price_wholesale ELSE 0 END) today,
    SUM(CASE WHEN rank = 2 THEN price_wholesale ELSE 0 END) yesterday
FROM (
    SELECT
        veg_id, price_wholesale, price_date,
        RANK() OVER (PARTITION BY veg_id ORDER BY price_date DESC) as rank
    FROM old_veg_prices
) p
INNER JOIN vegitables ON p.veg_id = vegitables.id
WHERE rank in (1,2)
GROUP BY veg_id

This Output result get when run query in database:

Following two table are used to get today price yesterday price and price average get from each product.

CREATE TABLE `vegitables` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `catagory` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_area` int(11) NOT NULL COMMENT 'Total area of culativate in Sri Lanka (Ha)',
  `total_producation` int(11) NOT NULL COMMENT 'Total production particular product(mt)',
  `annual_crop_count` int(11) NOT NULL COMMENT 'how many time can crop pre year',
  `short_dis` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `vegitables`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `vegitables`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

CREATE TABLE `old_veg_prices` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `veg_id` int(11) NOT NULL,
  `price_wholesale` double(8,2) NOT NULL,
  `price_retial` double(8,2) NOT NULL,
  `price_location` int(11) NOT NULL,
  `price_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ALTER TABLE `old_veg_prices`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `old_veg_prices`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
COMMIT;

I try this site to convert to MySQL query to query builder code. But it show some error's could find it out. Any Way i want to run this code in Laravel with any method??

"I've tried this site" and get an error. Post the CODE you've tried, and add the error to your question. Maybe then someone can help you — Gert B.
– Gert B., Commented Oct 26, 2021 at 10:05
The query won't show you yesterday and today rates, but the last two rates (one row could be day before yesterday and other could be few years ago). Is that the expected result? — Salman Arshad
– Salman Arshad, Commented Nov 1, 2021 at 12:53

Salman Arshad · Accepted Answer · 2021-11-02 12:03:14Z

1

+50

Your query will not return the data for yesterday and today; it will return the data for two most recent dates (e.g. if today is 2021-11-01 and most recent two dates for for carrots are 2021-10-25 and 2021-10-20 it will use those two dates). Using RANK() ... IN (1, 2) is also incorrect because it can return ranks such as 1 followed by 3 instead of 2.

To get today and yesterday prices you don't need window functions. Just use appropriate where clause and conditional aggregation:

SELECT vegitables.name
     , vegitables.image
     , vegitables.catagory
     , AVG(old_veg_prices.price_wholesale) AS avgwholesale
     , SUM(CASE WHEN old_veg_prices.price_date = CURRENT_DATE - INTERVAL 1 DAY THEN old_veg_prices.price_wholesale END) AS yesterday
     , SUM(CASE WHEN old_veg_prices.price_date = CURRENT_DATE THEN old_veg_prices.price_wholesale END) AS today
FROM vegitables
INNER JOIN old_veg_prices ON vegitables.id = old_veg_prices.veg_id
WHERE old_veg_prices.price_date IN (CURRENT_DATE - INTERVAL 1 DAY, CURRENT_DATE)
GROUP BY vegitables.id -- other columns from vegitables table are functionally dependent on primary key

The Laravel equivalent would be:

DB::table('vegitables')
    ->Join('old_veg_prices', 'old_veg_prices.veg_id', '=', 'vegitables.id')
    ->whereRaw('old_veg_prices.price_date IN (CURRENT_DATE - INTERVAL 1 DAY, CURRENT_DATE)')
    ->select(
        'vegitables.name',
        'vegitables.image',
        'vegitables.catagory',
        DB::raw('AVG(old_veg_prices.price_wholesale) AS avgwholesale'),
        DB::raw('SUM(CASE WHEN old_veg_prices.price_date = CURRENT_DATE - INTERVAL 1 DAY THEN old_veg_prices.price_wholesale END) AS yesterday'),
        DB::raw('SUM(CASE WHEN old_veg_prices.price_date = CURRENT_DATE THEN old_veg_prices.price_wholesale END) AS today')
    )
    ->groupBy(
        'vegitables.id',
        'vegitables.name',
        'vegitables.image',
        'vegitables.catagory'
    )
    ->get();

edited Nov 2, 2021 at 12:03

answered Nov 1, 2021 at 13:27

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nipun Sachinda Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1055 'slagro.vegitables.name' isn't in GROUP BY

Nipun Sachinda Over a year ago

above error happen when run the query

Salman Arshad Over a year ago

Add vegitables.name, vegitables.image, vegitables.catagory to the group by. But it should not have been a problem on MySQl 5.7 or MySQL 8 if vegitables.id defined as primary key.

Nipun Sachinda Over a year ago

thank you very much your valuable contribution

Rick James · Accepted Answer · 2021-10-28 19:33:59Z

1

"Query builder" features of abstraction products often leave out some possible SQL constructs. I recommend you abandon the goal of reverse engineering SQL back to Laravel and simply perform the "raw" query.

Also...

rank() OVER (PARTITION BY veg_id ORDER BY price_date DESC) as rank

requires MySQL 8.0 (MariaDB 10.2).

And suggest you avoid the alias "rank" since that is identical to the name of a function.

edited Oct 28, 2021 at 19:33

answered Oct 28, 2021 at 17:54

Rick James

144k15 gold badges144 silver badges254 bronze badges

14 Comments

Nipun Sachinda Over a year ago

i try raw query method also but it also getting error

Rick James Over a year ago

Please show us the complete error message.

Nipun Sachinda Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Nipun Sachinda Over a year ago

DB::select('myquery')->get();

Rick James Over a year ago

That GROUP message is pointing out that there are 2 prices for veg_id 1; hence the query is ambiguous and needs clarification. Cf ONLY_FULL_GROUP_BY

|

Collectives™ on Stack Overflow

Convert MySQL query to Laravel query builder code

2 Answers 2

4 Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related