connecteev

Thank you @snapey and @jcortes Apparently the laracasts notification emails were delayed so I'm just seeing your responses now.

Modifiers looks like the closest thing to what I was trying to do...but snapey's response is also a good one.

@martinfolkerts that's interesting, thanks for the reply. That said, the solution above works and is less painful than maintaining a separate unique list.

I'm just looking for a better way (possibly using faker->unique, faker seeds or something else I havent thought of)

Summary

Is there a better way to generate fake data with a 'unique' DB constraint?

Versions

PHP version: PHP 7.3.5 Faker version: "fzaninotto/faker": "^1.4",

I have a users table in laravel with:

• unique 'username'
• unique 'email'

I am using Faker to generate a large number of fake users for testing. However, I kept hitting an 'integrity constraint violation', so I started checking for uniqueness manually (below). My question is, is there a better way? I would love to use:

factory(User::class, 400)

but it doesn't seem to generate unique values for username and email. I need both of them to be individually unique.

        // Note: need a for loop to check for unique usernames and emails, otherwise could have instead used: factory(User::class, 400)
        for ($i = 0; $i < 400; $i++) {
            $fakeUserName = $faker->userName;
            while (User::where('username', '=', $fakeUserName)->exists()) {
                // echo "username $fakeUserName exists, attempt to find a unique username..\n";
                $fakeUserName = $faker->userName;
            }
            $fakeEmail = $faker->unique()->safeEmail;
            while (User::where('email', '=', $fakeEmail)->exists()) {
                // echo "email $fakeEmail exists, attempt to find a unique email..\n";
                $fakeEmail = $faker->unique()->safeEmail;
            }
            factory(User::class, 1)
                ->create([
                    'username' => $fakeUserName,
                    'email' => $fakeEmail,
                ]);
        }

Is there a way to improve on this?

Any thoughts on how to solve this problem?

@jlrdw thanks for the reply. This workflow involving manual backups sounds pretty bulletproof (and is the safe way to go), the downside is the manual checks and it doesn't rely on Laravel's abilities.

I'm wondering if there is a way to improve on this workflow in an automated way, using things like migrations etc. for the use cases I mentioned above. Not sure if this is possible, but I am ever curious to see if someone has a better way to do this.

@dalma thanks for the reply. It's interesting to see what others are doing successfully to manage their production data. How does Forge handle the 3 use-cases above with existing production data?

Btw I am not using Forge and don't plan to move to it, so I'd be very interested in what others in a similar boat are doing.

I have an existing Laravel application in production with an existing Database. I'd like to know what process others use to manage schema changes on a production DB, where the data is kosher and needs to be preserved after schema changes are applied.

Adding tables is relatively easy, because that table doesn't exist yet on production. But what process do you use to:

1. Delete a table (how do you tell the code to 'not try to migrate the data from that table'?)
2. add a column to one or more DB tables (say if the column has a required field? how do you tell the code to migrate the existing data after the schema changes are applied, without erroring out?)
3. remove a column from a DB table (how do you tell the code to 'not try to migrate the data from that column'?)

I obviously have used migrations a lot for local development, but I (think I) am missing some key concepts around how to manage Laravel data.

Is there a way to do all of this with Laravel migrations programmatically? ie without having to do a manual export of the DB and subsequent import?

@jlrdw Good tips, thanks...is there a free query designer I can use? I'm on a Mac and use Sequel Pro or Table Plus. I'm not sure what query designer means because I havent really used one.

@jlrdw thanks...I ended up doing a RAW query like this.

Here's the full code. Can this be optimized in any way? Your thoughts are appreciated.

    // Example: Test URL: http://localhost:8000/api/v1/likes/lessie.mcglynn/aut-saepe-fugit-sequi-molestiae-placeat-rerum?loggedinUserId=1234
    public function getCommentsReactedToByUserId(Request $request, $authorUsername, $postSlug)
    {
        if (!$request->has('loggedinUserId')) {
            return ["error" => "loggedinUserId not specified"];
        }
        $loggedinUserId = $request->loggedinUserId;

        $postId = Post::where('slug', $postSlug)->pluck('id');
        $postTopLevelCommentIds =
            Comment::where([
                ['deleted_at', '=', NULL],
                ['is_removed', '=', '0'],
                ['commentable_type', '=', "App\Post"],
                ['commentable_id', '=', $postId],
            ])
            ->pluck('id');

        $allNestedCommentIdsForPost = [];
        foreach ($postTopLevelCommentIds as $parentCommentId) {
            $allNestedCommentIdsForPost[] = ["id" => $parentCommentId];
        }
        foreach ($postTopLevelCommentIds as $parentCommentId) {

            // for each top-level comment, get all nested sub-comments and flatten the list to an array
            $nestedCommentIdsForComment = \DB::select(
                \DB::raw(
                    "SELECT id FROM (SELECT * FROM comments
                    WHERE deleted_at IS NULL AND is_removed=0 AND commentable_type LIKE \"App%Comment\"
                    ORDER BY commentable_id, id) products_sorted,
                    (SELECT @pv := :PARENT_COMMENT_ID) initialisation
                    WHERE FIND_IN_SET(commentable_id, @pv)
                    AND LENGTH(@pv := concat(@pv, ',', id))"
                ),
                array(
                    'PARENT_COMMENT_ID' => $parentCommentId,
                )
            );
            $allNestedCommentIdsForPost = array_merge($allNestedCommentIdsForPost, $nestedCommentIdsForComment);
        }

        $commentIds = array_column($allNestedCommentIdsForPost, 'id');

        $commentIdsLikedByUser = Reaction::where([
            ['is_removed', '=', '0'],
            ['deleted_at', '=', NULL],
            // ['reaction_type', '=', "like"],
            ['reactable_type', 'LIKE', "App%Comment"],
            ['user_id', '=', $loggedinUserId],
        ])
            ->whereIn('reactable_id', $commentIds)
            ->select('id', 'reactable_id as commentId')
            ->get();
        return new ReactionResource($commentIdsLikedByUser);
    }

select  id
from    (select * from comments
        where deleted_at is NULL AND is_removed=0 and commentable_type LIKE "App%Comment"
         order by commentable_id, id) products_sorted,
        (select @pv := '26') initialisation
where   find_in_set(commentable_id, @pv)
and     length(@pv := concat(@pv, ',', id))

thoughts on how to do this?

@irsyadadl any thoughts on https://laracasts.com/discuss/channels/eloquent/eloquent-query-to-get-counts-at-every-level-of-nesting-in-a-polymorphic-one-to-many-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)

The gist includes:

1. The models:

• app/Comment.php . (has a function called nestedComments() that gets comments recursively)
• app/Post.php
• app/Reaction.php

1. Controller (API) code to retrieve nested (recursive) comments on a post, with unlimited levels of nesting.

2. The SQL schema for posts, comments, reactions tables.

I am trying to solve this:

At each level of nested comments, I need counts of num_likes (number of reactions to the immediate comment) and num_comments (number of immediate sub-children) at each level of nested comments.

So I need an Eloquent Query to get Counts at every level of nesting in a Polymorphic one-to-many relationship.

I already have nested results coming back, but counts don't work (my current implementation of withCount( ) isn't consistent - I dont get counts beyond 2 levels deep....and as you will see in the code I have commented out below, it causes ~10 seconds of slowness...that's not an exaggeration).

See these commented-out blocks of code in

• PostsApiController.php L72-L83
• App/Comment.php L123-L144
• App/Comment.php L81-L103

How can you get the counts of num_comments and num_likes efficiently at unlimited levels of nested comments, without retrieving ALL of the comments and likes (reactions), which is what I am currently doing?

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:

1. The models:

• app/Comment.php . (has a function called nestedComments() that gets comments recursively)
• app/Post.php
• app/Reaction.php

1. Controller (API) code to retrieve nested (recursive) comments on a post, with unlimited levels of nesting.

2. 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 [email protected]_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?

@shez1983 great point about caching.

For "Returning all comments for a post" Paginating nested comments is tricky. What if you have comment #1 with 20-levels of nested comments (crude example)? Would a simple paginate() on the top-level comments be enough? How would you do this?

I have a Laravel application with logged-in users, posts, comments and likes (reactions).

I am designing an API to return all the data for a post (similar to this page here ). As you can see, the currently logged-in user can like / react to the main post, or any of the comments.

In my API, I return the post data, and the recursive comment data. However, I also need to know whether the currently logged-in user has "liked" (reacted to) the main post, and to the comments that are returned by the API. How would you design this API?

• Should this be a GET or POST API?
• Should it be "gated" by an Auth gate?
• What would the API signature look like?

Note that this API is going to be used to retrieve data about the post + comments + reactions, and the user could either be logged in or not logged in.

Thanks in advance for your input.

PS. I don't care all that much about it being a RESTFUL API, as long as it is secure and gets the job done.

@willvincent that makes sense to me. Thank you and I will keep this in mind - may move this logic to a cron later to speed things up.

@michalk I actually really like the optimization you did there. Even when I move to a cron, I don't have to move EVERYTHING to the cron. Thanks for the tip.

@lostdreamer_nl thanks....those approaches are interesting (parent-child vs nested set). This is what I have so far...based on your last suggestion. My application is going to be heavier on reads (versus writes/updates). Would you recommend any changes to this to speed things up?

I created a polymorphic 'comments' table as you suggested.

This is my comments table schema.

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `commentable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `commentable_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `comment_user_hash_parentid_unique` (`user_id`,`comment_hash`,`commentable_id`),
  KEY `comment_hash_index` (`comment_hash`),
  CONSTRAINT `user_fk_369255` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2048 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is a snippet from my Comments.php model:

class Comment extends Model
{
    use SoftDeletes;

    public $table = 'comments';


    /**
     * Get the owning commentable model.
     */
    public function commentable()
    {
        return $this->morphTo();
    }

    /**
     * Get all of the comments (single-level, not recursively)
     */
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }

    /**
     * Get all of the comments' comments recursively.
     */
    public function nestedComments()
    {
        // Get comments recursively, from https://laraveldaily.com/eloquent-recursive-hasmany-relationship-with-unlimited-subcategories/
        return $this->morphMany(Comment::class, 'commentable')->with('nestedComments');
    }

And this is my API / Controller code:

$max_top_level_comments = 20;

$post =
    Post::with([
        'comments' => function ($query) use ($max_top_level_comments) {
            $query
                ->where([
                    ['is_removed', '=', '0'],
                    ['deleted_at', '=', NULL],
                ])
                ->select('id', 'comment_hash', 'body', 'user_id', 'commentable_id', 'commentable_type', 'created_at')
                ->with([
                    'nestedComments' => function ($query) {
                        $query
                            ->where([
                                ['is_removed', '=', '0'],
                                ['deleted_at', '=', NULL],
                            ])
                            ->select('id', 'comment_hash', 'body', 'user_id', 'commentable_id', 'commentable_type', 'created_at');
                    },
                ])
                ->take($max_top_level_comments)
                ->get();
        },

    ])
    ->find(1);

When I change the primary key of my polymorphic 'comments' table from 'id' to a varchar, PHP runs out of memory. How can I fix this?

This is my comments table schema. I would like every request (CRUD) to be based off of comment_hash instead of 'id'. Can this actually be accomplished?

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `commentable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `commentable_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `comment_user_hash_parentid_unique` (`user_id`,`comment_hash`,`commentable_id`),
  KEY `comment_hash_index` (`comment_hash`),
  CONSTRAINT `user_fk_369255` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2048 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is a snippet from my Comments.php model:

class Comment extends Model
{
    use SoftDeletes;

    public $table = 'comments';


    /**
     * Customize the primary key for comment model. Results in 'out of memory'
     */
    protected $primaryKey = 'comment_hash';

    /**
     * Get the owning commentable model.
     */
    public function commentable()
    {
        return $this->morphTo();
    }

    /**
     * Get all of the comments (single-level, not recursively)
     */
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }

    /**
     * Get all of the comments' comments recursively.
     */
    public function nestedComments()
    {
        // Get comments recursively, from https://laraveldaily.com/eloquent-recursive-hasmany-relationship-with-unlimited-subcategories/
        return $this->morphMany(Comment::class, 'commentable')->with('nestedComments');
    }

And this is my API / Controller code which times out (this works perfectly and returns nested comments if dont change the Comments primary key from id to comment_hash, as seen above)

$max_top_level_comments = 20;

$post =
    Post::with([
        'comments' => function ($query) use ($max_top_level_comments) {
            $query
                ->where([
                    ['is_removed', '=', '0'],
                    ['deleted_at', '=', NULL],
                ])
                ->select('id', 'comment_hash', 'body', 'user_id', 'commentable_id', 'commentable_type', 'created_at')
                ->with([
                    'nestedComments' => function ($query) {
                        $query
                            ->where([
                                ['is_removed', '=', '0'],
                                ['deleted_at', '=', NULL],
                            ])
                            ->select('id', 'comment_hash', 'body', 'user_id', 'commentable_id', 'commentable_type', 'created_at');
                    },
                ])
                ->take($max_top_level_comments)
                ->get();
        },

    ])
    ->find(1);

This is the error I see in the browser when I try to hit the API:

Error:

This page isn’t working
localhost is currently unable to handle this request.
HTTP ERROR 500

Error logs in terminal:

[Fri Sep 27 10:08:08 2019] PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 163840 bytes) in /Users/kp/kb_backend_apis_laravel/vendor/composer/ClassLoader.php on line 444

Can this actually be accomplished? if so, how?

@snapey @jlrdw thanks, I understand...but I have my reasons (keeping the URL short at 4 characters, not having to futz around with PHP and make sure that with a MySQL trigger, consistency is achieved, etc.

@jlrdw I understand not every successful site (like truecar) does this. I'm just modeling off of https://dev.to/mindplay/comment/dm5i and want to keep the URLs as short as I possibly can.

I did manage to get a MySQL trigger working (above), so I'll keep looking for a solution here.

@lostdreamer_nl thank you, I didn't know about morphTo and morphMany, this is definitely a step in the right direction.

Does this return up to 3 levels of comments to post with ID=1? Is there an eloquent way to retrieve an unlimited tree of comments?

$post = Post::with('comments.comments.comments')->find(1);

Doing

        $post = Post::with('comments.comments.comments.comments.comments.comments.comments.comments')->find(1);

doesnt make much sense.

Also, each of my posts and comments can have one or more 'likes' (it is similar to 'comments' in that it is also polymorphic).

How can I retrieve a post with:

1. Unlimited nesting of comments
2. For each level of comments, retrieve the likes to that comment from the likes table.
3. likes to the post from the likes table

This doesn't quite work, I don't get the likes data back:

New attempt (your method, using commentable_id and commentable_type):

        $post = Post::with('comments.comments.comments')->with('likes')->find(1);

Older attempt (using a different method, neither of them work):

        // Note: if you dont also return author_id here, the author object returned below will be null
        $returnedCommentTree = $returnedCommentTree
            ->select('id', 'comment_hash', 'comment_on_type', 'comment_on_post_id', 'comment_on_comment_id', 'user_id', 'body', 'created_at')
            ->with([
                // Note: MUST also return the author_id field above
                'user' => function ($query) {
                    $query->where('deleted_at', NULL)
                        ->select('id', 'name');
                },

                'childrenComments' => function ($query) {
                    $query->where([
                        ['is_removed', '=', '0'],
                        ['deleted_at', '=', NULL],
                    ])
                        ->select('id', 'comment_hash', 'comment_on_type', 'comment_on_post_id', 'comment_on_comment_id', 'user_id', 'body', 'created_at')
                        ->with([
                            // Note: MUST also return the author_id field above
                            'user' => function ($query) {
                                $query->where('deleted_at', NULL)
                                    ->select('id', 'name');
                            },
                            'likes'
                        ]);
                },

                'likes'
            ]);

@snapey You can ignore the User ID, we can randomize it to any value. The comment_hash is more important.

Think of it like this..I am trying to map (approximately) 1.6M UNIQUE numbers (0-1,600,000) each to a UNIQUE 4-character string (comment_hash) in MySQL, with each character having at least 36 possible values (a-z0-9 if lowercase only, a-zA-Z0-9 if uppercase, which isn't the default "collation" in MySQL).

I'm sure there's a way to "map" values in a unique way instead of having to use a sub-optimal algorithm like md5 and do a check for duplicates. I know this is not an easy problem to solve...but I don't see why this wouldn't be (theoretically) possible.

Awesome...hope it helps others too. Thank you @jlrdw

@jlrdw @willvincent @whoisthisstud There's no way I can mark all of your answers "Best Answer", but I'd like to say I'm appreciative of the help all of ya'll have been. I'm going to mark mine the "best" because it's closest to what works and I hope it will help someone else in the future. The thoughts shared here have been eye-opening, though.

Turns out that my prior SQL query was incomplete (I was missing the where condition in my Raw SQL - and this one below actually works:)

$returnedPosts = Post::where([
            ['status', '=', 'published'],
            ['is_removed_by_admin', '=', '0'],
        ]);

$returnedPosts = $returnedPosts->orderByRaw("SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) DESC")->groupBy('id');

Unlike what I mentioned in my last comment above, it matches with the SQL query below:

select `id`, `title`, `slug`, `author_id`, `featured_image_caption`, `reading_time_minutes`, `num_comments`, `num_likes`, `created_at`, `updated_at` 
from `posts` 
where (`status` = 'published' and `is_removed_by_admin` = '0') 
and (`created_at` >= "2000-01-01T00:00:00.000000Z") 
and `posts`.`deleted_at` is null 
group by `id` 
order by SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) DESC 
limit 15 offset 0;

;

I was missing the where (`status` = 'published' and `is_removed_by_admin` = '0') conditions and so the results were inconsistent previously.

@jlrdw I did read most of your comments and examples, though I have to admit some of it was a bit complicated. @willvincent I like the cron approach as well, I might give that a go in the future if this API becomes slow. I agree with you here: There would be some lag time between sort ordering getting updated, but the drastic ease of querying may be a worthwhile tradeoff to some of the realtimeyness

Another approach that others could consider (which won't work in this specific case, unfortunately): a DB trigger to calculate the hotness_score every time a row is inserted or updated. This would have worked except for the fact that my algorithm relies on NOW() and calculates the time difference, and that would quickly become stale if we relied on updating the hotness_score on create/update only.

@jlrdw that's a pretty detailed (and complex) example, thanks for sharing it. Looks like you and @willvincent are Laracasts veterans.

@whoisthisstud thanks but I'd prefer not to loop through the results in a for loop, this would slow down the API and I'd prefer to use something "native" to Laravel if that makes sense.

@jlrdw I just discovered orderByRaw() and groupBy() and tried this, but it doesn't quite work.

$returnedPosts = Post::where([
            ['status', '=', 'published'],
            ['is_removed_by_admin', '=', '0'],
        ]);

$returnedPosts = $returnedPosts->orderByRaw("SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) DESC")->groupBy('id');

If I run the same raw SQL query in Sequel Pro against the same table, I get a different order of results:

SELECT id, title, created_at, num_comments, num_likes, CHAR_LENGTH(body),
SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) AS hotness_score 
from posts 
-- where created_at > '2019-09-21 22:14:55'
GROUP BY id 
order by hotness_score desc 
-- limit 100
;

which leads me to believe that the orderByRaw above isn't correct.

@jlrdw @willvincent I marked this as "not resolved" because there's one thing I'm unsure about.

This is the query for retrieving all posts.

        $returnedPosts = Post::where([
            ['status', '=', 'published'],
            ['is_removed_by_admin', '=', '0'],
        ]);
        $returnedPosts = $returnedPosts->orderBy('created_at', 'DESC');

And this is the RAW SQL query for sorting posts by hotness score (my algoritm).

SELECT id, title, created_at, num_comments, num_likes, CHAR_LENGTH(body),
SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) AS hotness_score 
from posts 
where created_at > '2019-09-21 22:14:55'
GROUP BY id 
order by hotness_score desc 
limit 100;

How would you replace:

$returnedPosts = $returnedPosts->orderBy('created_at', 'DESC');

with the algorithm? Is there a clean way to do that?

@willvincent @jlrdw thank you! I'll leave it as RAW SQL.

@siangboon that's interesting...means one should not try to index the comments too aggressively. Thanks for sharing that.

Per my last comment, the problem still remains, however.

I'm not very adept with Eloquent so I hope someone more familiar with it can help.

I have a post and comments table (DB structure below). I am looking for a Laravel Eloquent way to return a tree of data containing the post being loaded and the recusrive tree of comments belonging to that post.

This is what I currently have in my API:

    public function show($authorSlug, $postSlug)
    {
        // Only return post with: status="published" AND is_removed_by_admin=false
        $returnedPost = Post::where([
            ['slug', '=', $postSlug],
        ])
        ;

        // Note: if you dont also return author_id here, the author object returned below will be null
        $returnedPost = $returnedPost
            ->select('id', 'title', 'slug', 'body', 'created_at', 'author_id')
            ->with([
                'comments' => function ($query) {
                    $query->where([
                        ['comment_on_type', '=', 'post']
                    ])->select('id', 'comment_on_post_id', 'comment_on_comment_id', 'body')
                        ->with(['comment_on_post', 'comment_on_comment']);
                },
            ]);

        // For posts on the user's profile page - change this to search by profile handle or username instead (currently using 'id')
        $returnedPost = $returnedPost->whereHas('author', function ($query) use ($authorSlug) {
            // Query the name field in author (users) table
            $query->where('id', $authorSlug);
        });

        // $returnedPost = $returnedPost->take(1)->get();
        $returnedPost = $returnedPost->firstOrFail();

        return new PostResource($returnedPost);
    }

With my API when I go to: http://localhost:8000/api/v1/open/posts/2/ut-eaque-laborum-dolore-vitae

The output Json currently looks like this https://jsonformatter.org/json-viewer/1cc007

I need to change this so that I get a tree of comments from any level, similar to what's seen here: https://dev.to/seattledataguy/the-interview-study-guide-for-software-engineers-764/comments or here (to see the sub-tree of a parent comment): https://dev.to/scottshipp/comment/fcmn or https://dev.to/eanx/comment/fep0

This is the DB table structure:

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `author_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_fk_272473` (`author_id`),
  CONSTRAINT `author_fk_272473` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comment_on_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `comment_on_post_id` int(10) unsigned DEFAULT NULL,
  `comment_on_comment_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `comment_on_post_fk_376132` (`comment_on_post_id`),
  KEY `comment_on_comment_fk_376133` (`comment_on_comment_id`),
  CONSTRAINT `comment_on_comment_fk_376133` FOREIGN KEY (`comment_on_comment_id`) REFERENCES `comments` (`id`),
  CONSTRAINT `comment_on_post_fk_376132` FOREIGN KEY (`comment_on_post_id`) REFERENCES `posts` (`id`),
  CONSTRAINT `user_fk_369255` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

@snapey the comment will have a URL as soon as it is posted

Coming back to the problem where I want to obfuscate the comment IDs, and I want to use a pure MySQL-only solution. Here's what I have managed to get working (using a MySQL trigger to update the row that was inserted with a comment_hash value. The problem is I get a "Duplicate Entry" error. I know hashes are not unique but how can I map a UNIQUE _comments.id value to a UNIQUE 4-character hash?)

DROP TABLE IF EXISTS _comments;
CREATE TABLE IF NOT EXISTS _comments (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `comment_hash` VARCHAR(255) NOT NULL,
    `user_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `comment_user_hash_parentid_unique` (`user_id`,`comment_hash`)
)  ENGINE=INNODB;


DROP TRIGGER IF EXISTS `test_add_comment_hash_on_insert_into_comments_table`;
DELIMITER //
CREATE TRIGGER test_add_comment_hash_on_insert_into_comments_table
BEFORE INSERT ON _comments
FOR EACH ROW
BEGIN
    SET NEW.comment_hash = right(md5(LAST_INSERT_ID()+1),4);
END
//
DELIMITER ;

And then in my seeder:

<?php

use Illuminate\Database\Seeder;

use Faker\Generator as Faker;

// Call using command: php artisan db:seed --class=CommentsFakeDataSeeder
class WtfFakeDataSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run(Faker $faker)
    {
        echo "Seeding _comments... \n";

        DB::statement('SET FOREIGN_KEY_CHECKS=0;');

        $fake_comments = [];

        for ($i = 0; $i < 10000; $i++) {
            $fake_comments[] = [
                'user_id'   => 1,
            ];
        }
        DB::table('_comments')->insert($fake_comments);

        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }
}

I invoke the seeder with: php artisan db:seed --class=WtfFakeDataSeeder

But I always end up with an integrity constraint violation

Doctrine\DBAL\Driver\PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-849b' for key 'comment_user_hash_parentid_unique'")

With lowercase-only alphanumeric characters allowed in the 4-character comment_hash, we should have 36^4 = 1679616 values. Yet, collisions occur with < 10K inserts as you can see below. md5() isn't the right solution here..do you know one that is? id's are always unique...whether or not I combine them with userID (to introduce more entropy), how can I map them to UNIQUE 4-character hash values?

@snapey 100% agree on the topic / post title. I am talking about comments to posts, and they dont typically have a title.

@snapey that's a good point I hadn't thought of before....SEO is a huge consideration.

Thanks for the link to creating hashids in laravel, that's quite interesting.

But for long comments like you see here, how do you create long URLs (for SEO)? Truncate and slugify the comment body? How would you ensure that they are unique?

Any thoughts on the above would help

@snapey I do have a user_id column on the table to know who the comment belongs to.

I understand that slugs are the most conventional and simple way to go. But there is a reason for this. The idea is to make the comment slug as short as possible, to make the URL as short as possible...thats why I am avoiding long slugs. Is there a way to do what I am trying to do here (unique combination of 2 fields in MySQL?) The other less-attractive option would be to increase the length to 6 characters and make the commend UUID individually unique...which isn't ideal.

@siangboon I'm trying to keep the URL as short as possible. I realize that there are other more pressing things, but I want to solve this problem as well. Pushing it out to later is not a good option because then I'll have to change the URL format, which will have SEO implications. I'd rather deal with this and get it right now, one time and forever.

@rameezisrar thanks for the tip here. How can I use this to generate a combined unique value for userId + commentId? An example would be helpful.

@terreporter here is a simplified SQL statement I would like to use for the algorithm.

SELECT id, title, created_at, num_comments, num_likes, CHAR_LENGTH(body),
SUM( (IFNULL(num_comments, 0)*0.5) + (IFNULL(num_likes, 0)*0.4) + (CHAR_LENGTH(body)*0.1) ) / (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(created_at)) AS hotness_score 
from posts 
where created_at > '2019-09-21 22:14:55'
GROUP BY id 
order by hotness_score desc 
limit 100;

This doesn't do a join with the comments / reactions tables, and so this algorithm doesn't consider

* Date of last comment (Recency of Comments, from comments table, which contains post_id)
* Date of last like / reaction (Recency of Likes / Reactions, from reactions table, which contains post_id))

Is there a way to convert the above SQL to a Laravel Eloquent? And maybe an eloquent way to include date of last comment and reaction?

There already are answered questions on how to generate unique, random alphanumeric strings in MySQL. That is not the question.

I am trying to generate a 4 character, random alphanumeric string in MySQL such that the COMBINATION of that field and another are unique in MySQL. In other words, given an existing field in the table (userID), I need a 4 character, random alphanumeric string for another field in the same table (commentUID), such that the combined userID+commentUID is unique. Note that commentUID by itself is not necessarily unique - 2 or more users with different userIDs can have the SAME commentUID value.

Background: I have a users table, and a comments table. I need a unique combination for: the user's username (or ID) and the Unique ID of the comment they leave, so that every comment in the system can be uniquely identified in the URL, while also keeping the comment UIDs (and therefore URLs) as short as possible.

The unique ID for the comment should be 4 alphanumeric characters, because each user is not likely to leave more than 36^4 = 1.6M comments (36 denotes all possible values from a-z0-9, lowercase only). An example of this is https://dev.to/mindplay/comment/dm5i

How can I do this as a pure MySQL-only solution, such that the commentID is generated when a new record is inserted into the comments table?

I am building a content platform and have a Laravel API for my news feed (infinite-scrolling feed like the Linkedin feed, Facebook feed, etc). I need to order the posts in my news feed based on the algorithm below (each parameter is going to have a weight, and I want to use these to calculate and order the results / Posts by the composite score).

                Parameters for algorithm:
                Num Comments
                Num Likes
                Date Created (Recency of Post)
                Date Updated (Recency of Post)
                Date last comment (Recency of Comments)
                Date last like / reaction (Recency of Likes / Reactions)
                Length of Post?
                Fast Rising: Number of likes / comments in past X <hours / days>

The DB structure looks like this: Post hasMany Comments Post HasMany Reactions ('likes')

The schema is here: https://gist.github.com/connecteev/0e0d6751a7056af00ead267189a5864e

How can you do an OrderBy in Laravel based on

1. a composite score or combined metric from multiple columns
2. values from related tables (comments, reactions) (like what I am trying to do above)?

Thank you all!

Figured it out: Turns out you cant use get() and paginate() together here. The paginate() works fine without the get()

class PostsApiController extends Controller
{
    use MediaUploadingTrait;

    public function index()
    {
        return new PostResource(
            Post::where([
                ['status', '=', 'published'],
            ])
                ->select('id', 'title', 'slug')
                //->take(3)
                ->paginate(5)
        );
    }
}

Yeah that video doesn't have anything to do with pagination

@jlrdw Just saw it and it's not what I am looking for.

I am using an API generator called quickadminpanel and for that reason I cannot stray too far from the implementation above - so I need to a way to paginate the PostResource above.

How to paginate this with Ajax? This is my Posts API which is consumed by my front-end (in Vue/Nuxt). I am trying to implement an infinite scroll but ->paginate(5) (commented out) does not work.

class PostsApiController extends Controller
{
    use MediaUploadingTrait;

    public function index()
    {
        return new PostResource(
            Post::where([
                ['status', '=', 'published'],
            ])
                ->select('id', 'title', 'slug')
                //->take(3)
                //->paginate(5)
                ->get()
        );
    }
}

same problem here. How can one use guzzlehttp with php artisan serve --port=8000 on localhost?

Thank you @ejdelmonico It does look like scopes are what I would need. Do you know of any code or repos that do all of this in a clean/simple way, ideally in Laravel + Nuxt (otherwise, Laravel + Vue or vanilla Js would be helpful as well).

@bobbybouwmann thank you. That helps - I'm not sure if it's just me, but as good as the Laravel documentation is, I find passport to be incredibly complicated and confusing...the Passport documentation is detailed but a bit too advanced...it could be dumbed down a bit.

The question is, how do I use Passport to authenticate different types of API requests....I apologize for the lack of detail in the original question but I didn't want to overwhelm people!

Is the Password Grant token appropriate for all of these.... I have 3 broad categories of data available in the APIs:

Type 1: Fully open, no Auth, not tied to a user: Examples:

1. GET /api/tags - API that gets all tags, this should not require authentication + authorization. This API would be used to display all tags on the /tags page, for example.

Type 2: Admin-only endpoints: Fully closed, not available to anyone, but for me (the Admin). Examples:

1. POST / PATCH / DELETE /api/tags - APIs that create / update / delete (global) tags, these should only be accessible by me (Admin)
2. GET /api/users - should only be accessible by me (Admin)

Type 3: User-specific endpoints, Available to a logged-in user only (and of course the Admin). Examples:

1. POST / PATCH / DELETE /api/user/1/settings - APIs that create / update / delete (user-specific) data, these should only be accessible by a logged-in user, and by me (Admin)

Is the Password Grant token appropriate for all of these? And a long-shot, do you know of any code or repos that do all of this in a clean/simple way, ideally in Laravel + Nuxt (otherwise, Laravel + Vue or vanilla Js would be helpful as well).

I'd (very much) appreciate your response to these.

@bobbybouwmann you've helped me before and I hope you can save the day here again!

I am creating a niche community site+forum where users can sign up, log in, create posts and follow each other.

My tech stack consists of backend APIs in Laravel (using Laravel passport), and a front-end in Vue.js / Nuxt.

I can access all the APIs through Postman, where I call http://localhost:8000/oauth/token to request the token https://laravel.com/docs/master/passport#requesting-password-grant-tokens and then subsequently, I call an API using the provided access_token / bearer token, like http://127.0.0.1:8000/api/v1/tags

My question is, do I NEED a full oauth flow -- my front-end will indefinitely need access to the backend APIs / data in Laravel, but how does the client get access to the data without going through a 2-way handshake with each user session, which seems like overkill? Do I need a Password Grant Token, an Implicit Grant Token, a Personal Access Token, something else? How do I "whitelist" my front-end javascript client while also somewhat protecting my data from bad use?