Some SQL query situation, Doctrine/Eloquent on Laravel

https://www.sitepoint.com/laravel-doctrine-best-of-both-worlds/

https://stackoverflow.com/questions/30231862/laravel-eloquent-has-with-wherehas-what-do-they-mean

Object Relation mapping is hard as fk, so even with Active Record or Data Mapper, ORM ... It somehow sometime eager load / relation seem not work as expected.

=> Try figure out why condition / filter on whereHas, hasOne()... not work.

ex. hasOne(UserReview, 'shop_id', 'shop_id')->orderby('created_at', 'desc')->take(1);

Try use raw query first.
It is somehow / maybe SQL, relational DB knowledge wrong, not Eloquent bug.

https://songmeanings.com/songs/view/57458/

https://stackoverflow.com/questions/48445480/mysql-order-by-from-subquery-lost-by-group-by

https://www.quora.com/Can-I-sort-faster-if-I-convert-strings-to-numbers

https://stackoverflow.com/questions/16620169/do-algorithms-sort-integers-and-strings-with-the-same-time-consistency

select max(id) group by for ie. get latest user comment/review per shop/place

https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column


Queries:


select shop_id, substr(shop_name, 1, 10), substr(place_id, 1,10), latitude, longitude from `ShopData` where exists (select * from `UserReviewData` where `ShopData`.`shop_id` = `UserReviewData`.`shop_id` and `user_id` = '5816bb0d908ff') and `ShopData`.`deleted_at` is null;


select shop_id, substr(shop_name, 1, 10), substr(place_id, 1,10), latitude, longitude from `ShopData` where exists (select * from `UserReviewData` where `ShopData`.`shop_id` = `UserReviewData`.`shop_id` and `user_id` = '5816bb0d908ff' order by created_at desc) and `ShopData`.`deleted_at` is null;

select shop_id, substr(shop_name, 1, 10), substr(place_id, 1,10), latitude, longitude from `ShopData` where exists (select * from `UserReviewData` where UserReviewData.shop_id = ShopData.shop_id and `user_id` = '5816bb0d908ff' order by `created_at` desc) and `deleted_at` is null

select shop_id, substr(shop_name, 1, 10), substr(place_id, 1,10), latitude, longitude from `ShopData` where shop_id in (select distinct(shop_id)  from `UserReviewData` where UserReviewData.shop_id = ShopData.shop_id and `user_id` = '5816bb0d908ff' order by `created_at` desc) and `deleted_at` is null ORDER BY FIELD(ShopData.shop_id, (select distinct(shop_id) from `UserReviewData` where UserReviewData.shop_id = ShopData.shop_id and `user_id` = '5816bb0d908ff' order by `created_at` desc));
=> order by field seem work but result not correct

-- Use review_id seem better and still correct?
select review_id, review_create_at, created_at, shop_id  from `UserReviewData` where  `user_id` = '5816bb0d908ff' order by review_id desc limit 50;

-- so only join ?
select DISTINCT(URD.shop_id), SUBSTR(shop_name, 1, 10), SUBSTR(place_id, 1,10), latitude, longitude, URD.created_at FROM `ShopData` INNER JOIN UserReviewData URD ON ShopData.shop_id = URD.shop_id  AND URD.`user_id` = '5816bb0d908ff' AND ShopData.`deleted_at` IS NULL ORDER BY URD.`created_at` DESC LIMIT 100;

--> DESC + group by shop_id (to limit only 1 shop-review) => it get oldest review not newest.

=>
select URD.shop_id, SUBSTR(shop_name, 1, 10), SUBSTR(place_id, 1,10), latitude, longitude, URD.created_at FROM `ShopData` INNER JOIN UserReviewData URD ON ShopData.shop_id = URD.shop_id  AND URD.`user_id` = '5816bb0d908ff' AND ShopData.`deleted_at` IS NULL  ORDER BY URD.`created_at` DESC  LIMIT 100;



592c2545eb818 have many review per shop


max(URD.review_id)

select created_at, max(review_id), shop_id, user_id  from UserReviewData where shop_id like '592c2545eb818' and user_id = '5816bb0d908ff';
+---------------------+----------------+---------------+---------------+
| created_at          | max(review_id) | shop_id       | user_id       |
+---------------------+----------------+---------------+---------------+
| 2017-08-14 10:33:15 |          25792 | 592c2545eb818 | 5816bb0d908ff |. <== max id is correct, created_at is not
+---------------------+----------------+---------------+---------------+

ORDER here have no effect since max(review_id) will return latest record.
So be carefull with misleading created_at column.

select created_at, min(review_id), shop_id, user_id  from UserReviewData where shop_id like '592c2545eb818' and user_id = '5816bb0d908ff' order by created_at desc;

We can use max(created_at) but it may be more expensive in the sense of computing algorithm.
+-----------+---------------------+---------------+---------------+
| review_id | max(created_at)     | shop_id       | user_id       |
+-----------+---------------------+---------------+---------------+
|      7165 | 2018-07-21 17:48:45 | 592c2545eb818 | 5816bb0d908ff |
+-----------+---------------------+---------------+---------------+
And what we want is just shop_id, and may be review_id not created_at.
What we 'want' make sense here. We want to get latest data based on review_id OR created_at, these information is needed but what we 'want'
the result to DISPLAYED can not contain created.


select created_at, max(review_id), shop_id, user_id  from UserReviewData where  user_id = '5816bb0d908ff' limit 100;

max on select only return 1

So we have to group by shop_id first.

select created_at, max(review_id), shop_id, user_id  from UserReviewData group by shop_id having  user_id = '5816bb0d908ff' order by created_at desc limit 100;

OK
+---------------------+----------------+---------------+---------------+
| created_at          | max(review_id) | shop_id       | user_id       |
+---------------------+----------------+---------------+---------------+
| 2019-01-08 22:53:06 |          28526 | 5c34ab41d532e | 5816bb0d908ff |
| 2019-01-08 22:49:04 |          28523 | 5c34aa502f284 | 5816bb0d908ff |

having user_id = 'xxx' and shop_id = '592c2545eb818'
having with and (many condition) not work, it only effect first having clause.

select created_at, max(review_id), shop_id, user_id  from UserReviewData group by shop_id having  user_id = '5816bb0d908ff' order by created_at desc limit 100;

Order by created_at make less correct than review_id but it make sense for review created date.

///
select created_at, max(review_id), shop_id, user_id  from UserReviewData where  user_id = '5816bb0d908ff' group by shop_id order by review_id desc limit 100;


without limit it return 387 records.
not 620
y? => Where have to inside select not after having;

select * from UserReviewData where user_id = '5816bb0d908ff' group by shop_id ;

This having work
-- work but not expected
select created_at, max(review_id), shop_id, user_id  from UserReviewData where  user_id = '5816bb0d908ff' group by shop_id  having shop_id = '592c2545eb818' order by review_id desc limit 100;
+---------------------+----------------+---------------+---------------+
| created_at          | max(review_id) | shop_id       | user_id       |
+---------------------+----------------+---------------+---------------+
| 2017-08-14 10:33:15 |          25792 | 592c2545eb818 | 5816bb0d908ff | <== 1 row but created wrong
+---------------------+----------------+---------------+---------------+

/// => max as review_id to fix order by not work
select created_at, max(review_id) as review_id, shop_id, user_id  from UserReviewData where  user_id = '5816bb0d908ff' group by shop_id order by review_id desc limit 1000;


https://odino.org/redis-slow-with-php-think-again/

Yeah config:cache and route:cache
 https://blog.pusher.com/optimizing-performance-laravel/
Be careful with these config, make sure clean route, config on deployment and cache em all after done.
Local dev/test need attention too.


cache all spot vs cache some spot.
Based on latest shop (as gmap ?) or more complex algorithm based on user behaviors.

If use latest spot and range 30-50km from latest one?
It require only some modify on query / filter data before caching.
But updating seem  more complicated since we have  to ensure caching correctly and keep cache set small.

https://stackoverflow.com/questions/39011241/laravel-dd-function-limitations

Latest 2 comments each post

select p.content post_content,
       c.content comment_content

from posts p
left join comments c on
    (p.id=c.post)
    and
    c.id>
    (select id from comments 
        where post=p.id
        order by id DESC LIMIT 2,1)

https://stackoverflow.com/questions/19905612/mysql-select-2-latest-comments-for-each-posts

My situation Shop::with too many thing: photo, review, comment like etc. => So it too risky and time consuming to refactor these query or keep track of SQL with logic code.
Relation is very convenient on pull data but for custom, filter it seem slower.

https://stackoverflow.com/questions/41520774/how-to-build-nested-query-in-codeigniter

get_compiled_select()

seem legit bc after all query builder seem to be a text converter.

Search core CI not found => may be embedded or some mechanism like AndWith in Doctrine.

SELECT r.*

FROM table AS r

    JOIN (

        SELECT MAX(t.created_on) AS created_on

        FROM table AS t

        GROUP BY YEAR(t.created_on), MONTH(t.created_on)

    ) AS x USING (created_on)