thoughts on how to do this?
Use Eloquent to get a flat list from polymorphic one-to-many nested relationship
Quick Side Note: I have more than open question I am trying to solve based on this same code. Also see this.
I have the following schema and code in my Laravel application for one-to-many polymorphic relations
- Users create Posts
- Post HasMany Comments
- Post HasMany Reactions
Here,
- Comment is polymorphic on Post or Comment (commentable_id and commentable_type tell you if the comment is on a Post or a Comment)
- Reaction is polymorphic on Post or Comment (reactable_id and reactable_type tell you if the reaction ('like') is on a Post or a Comment)
This gist includes:
- The models:
- app/Comment.php . (has a function called nestedComments() that gets comments recursively)
- app/Post.php
- app/Reaction.php
-
Controller (API) code to retrieve nested (recursive) comments on a post, with unlimited levels of nesting.
-
The SQL schema for posts, comments, reactions tables.
I am trying to solve this:
Given a parent ID (of post or comment), and the ID of a logged-in user, I want a flat list containing all the ids of comments (belonging to the parent) that are liked ('reacted' to) by the logged-in user ID.
And I need to implement the following SQL query (or equivalent) in Eloquent, to address this use-case.
SET @PARENTID=1;
SET @LOGGED_IN_USERID=1;
SELECT id, reactable_id, reactable_type, user_id
FROM (select * from reactions WHERE deleted_at IS NULL
AND reaction_type='like'
-- AND reactable_type LIKE "%App%Comment%"
AND user_id=@LOGGED_IN_USERID
ORDER BY reactable_id ASC, id ASC) comments,
(select @pv := @PARENTID) initialisation
WHERE find_in_set(reactable_id, @pv) > 0
AND @pv := concat(@pv, ',', id)
;
The output looks like this: https://1ce.org/1#SkU_OOCDB
How can I do this cleanly / elegantly in Eloquent? I want the results to be a flat Json. Can you re-use the code I have already written, or do I need something different?
Please or to participate in this conversation.