connecteev

I have a nested structure of comments. A comment can be made on either a post, or on some other comment (or sub-comment).

Therefore, the possible values for comments.commentable_type can be set to either: App\Post or App\Comment

comments.commentable_id is the id of the parent (Post or Comment) that the current comment was made on.

Each post has a field (num_comments) for the count of the number of comments + sub-comments under it. Similarly: Each comment has a field (num_comments) for the count of the number of comments + sub-comments under it. Note that num_comments is NOT a count of just the direct children (child comments) at the current depth. It should be an aggregate count of all comments below the current depth of the nested tree.

Here's my schema:

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,
  `num_comments` int(11) NOT NULL,
  `num_likes` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_fk_403952` (`user_id`),
  CONSTRAINT `user_fk_403952` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_removed` tinyint(1) DEFAULT '0',
  `comment_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `num_comments` int(11) NOT NULL,
  `num_likes` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `commentable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `commentable_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `comment_userid_commenthash_unique` (`user_id`,`comment_hash`),
  KEY `post_fk_655895` (`post_id`),
  CONSTRAINT `post_fk_655895` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  CONSTRAINT `user_fk_369255` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The problem:

I need to create a MySQL trigger that would do the following: When a new comment, comment_B, is ADDED (on another comment, comment_A), we want to update: num_comments for comment_A (should update from 2 to 3, in this example) num_comments for comment_A's parent comment (comment_X in this example, should update from 7 to 8) and so on, up the chain, until we reach the post (Post_1 in this example)

Before Adding Comment_B:

Post_1 (num_comments=12)
|---comment_X (num_comments=7)
|        |-------comment_X1 (num_comments=1)
|        |           |-------comment_XX1 (num_comments=0)
|        |           
|        |-------comment_X2 (num_comments=0)
|        |-------comment_X3 (num_comments=0)
|        |
|        |-------comment_A (num_comments=2)
|                    |-------comment_A1 (num_comments=0)
|                    |-------comment_A2 (num_comments=0)
|
|---comment_Y (num_comments=1)
|        |-------comment_YY (num_comments=0)
|
|---comment_Z (num_comments=1)
|        |-------comment_ZZ (num_comments=0)

After Adding Comment_B:

Post_1 (num_comments=13)
|---comment_X (num_comments=8)
|        |-------comment_X1 (num_comments=1)
|        |           |-------comment_XX1 (num_comments=0)
|        |           
|        |-------comment_X2 (num_comments=0)
|        |-------comment_X3 (num_comments=0)
|        |
|        |-------comment_A (num_comments=3)
|                    |-------comment_A1 (num_comments=0)
|                    |-------comment_A2 (num_comments=0)
|                    |-------comment_B (num_comments=0)
|
|---comment_Y (num_comments=1)
|        |-------comment_YY (num_comments=0)
|
|---comment_Z (num_comments=1)
|        |-------comment_ZZ (num_comments=0)

(something similar should happen when a comment is DELETED): When a comment, comment_B, is DELETED (from another comment, comment_A), we want to update: num_comments for comment_A (should update from 3 to 2, in this example) num_comments for comment_A's parent comment (comment_X in this example, should update from 8 to 7) and so on, up the chain, until we reach the post (Post_1 in this example)

This is what I have tried so far (this is VERY bare-bones and doesn't even attempt to bubble the updates up the tree.....and yet this does not work)

-- Trigger to update comments.num_comments when comment added (to the comment or a comment belonging to the comments's sub-comment tree, identified by id)
CREATE TRIGGER update_comments_num_comments_on_insert_comment AFTER INSERT ON comments 
FOR EACH ROW
  UPDATE comments
    SET num_comments = (SELECT count(*) FROM comments WHERE deleted_at IS NULL AND is_removed=0 AND commentable_type="App\Comment" AND commentable_id=NEW.commentable_id)
   WHERE id=NEW.commentable_id;

And to test it, I tried the following comment insert:

-- Test: Add comment on Comment (which is a comment on post with id#1)
INSERT INTO comments(body, is_removed, num_comments, num_likes, created_at, user_id, post_id, commentable_type, commentable_id)
VALUES('Add Comment to Trigger update_comments_num_comments_on_insert_comment', 0, 0, 0, NOW(), 1, 1, "App\Comment", 1);

But it gives an error:

Can't update table 'comments' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

How can I get the trigger working, and have it so that it bubbles up and updates num_comments all the way up the nested structure of comments? Note: if we don't figure out how to update the num_comments on the Post, that's fine, I can easily create a separate trigger for that (it's relatively straightforward since I already have a post_id field for every comment, making it easy to query the comments table for post_id and update posts.num_comments every time a comment is inserted or deleted)

Does it make sense to use something like http://www.mysqltutorial.org/mysql-rollup/ ? A working MySQL trigger would be much appreciated.

I didn't know that. Thank you @rodrigo.pedra !

Hi @rodrigo.pedra, this is phenomenal, thank you for the detailed response. I spent the day thinking and trying out your solutions....

I do have to eventually get to the point of adding the post_id to the comments table....but for now, this works quite well :)

SELECT post_id
FROM (
    SELECT
        @post_id := (SELECT commentable_id FROM comments WHERE id = @comment_id and commentable_type = 'App\Post') AS post_id,
        @comment_id := (SELECT commentable_id FROM comments WHERE id = @comment_id and commentable_type = 'App\Comment') AS comment_id
    FROM
        comments, (SELECT @comment_id := 3500, @post_id := NULL) AS initialization
    WHERE @post_id IS NULL
) AS recurse
WHERE post_id IS NOT NULL;

Thank you again..and I will be coming back to this to try the other approaches you suggested.

@rodrigo.pedra hi again! Thank you for your help last time, and now with this.

To address your first comment: You're right, I should probably include a post_id relation in my comments table / model, and then the problem becomes trivial. That also is the right way to do things, I'm sure. The problem is I don't have this relation already, and have a lot of code that is working fine without it. I don't have any unit tests (I know, I should add some, and that's on my roadmap...just not something I can get into now, because that would be quite a big tangent). To reduce the number of moving parts for now I am trying not to introduce a post_id field.

On your second comment: I am on MySQL 5.7 (sorry I should have mentioned this earlier)

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Is there a way to get your solution working with 5.7? Btw, what is newHasOne? I don't see that defined...curious if something is missing in the snippet. Again, thanks for your help.

@artcore thank you for sharing your solution...I really appreciate it. I tried but could not get this to work....I am not sure how to use your code.

@jatinkumar thank you as well. I also tried your solution (a simple loop), with some edits:

        $commentId = 17;

        $k = [];
        do {
            $myComment = Comment::find($commentId);
            $k[] = $commentId;
            $commentId = $myComment['commentable_id'];
        } while ($commentId && $myComment['commentable_type'] == "App\Comment");

This works by navigating up the hierarchy, but wondering if there's a more efficient way....maybe with a single MySQL query..

Agreed. Still trying to find an efficient MySQL query to do this, though. I found https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query and tried these

select @pv:=id as id, comment_hash, commentable_id, commentable_type, body
from comments
join
(select @pv:=15)tmp
where [email protected];

select
    @pv:=c1.id as id
  , c2.body as parent_body
  , c1.body body
  , c1.commentable_id
  , c1.commentable_type
from comments c1
join (select @pv:=15)tmp
left join comments c2 on c2.id=c1.commentable_id -- optional join to get parent name
where [email protected];

but honestly have no clue what I am doing. None of these are giving the result I expect (post id#5) I (think?) these are going down the hierarchy chain instead of up.

@tray2 would something like this be efficient in your opinion? https://stackoverflow.com/a/29972187/11491070

Though I have to admit, I do find it a bit confusing. Any idea how I would need to modify the code in StackOverflow? Some sample code would be helpful!

@tray2 yes I am using MySQL. Looking for a Laravel-way of doing the hierarchical queries.

Figured it out. Here's the complete solution that would work if we want to include a js (or mjs) file in ONE nuxt.js page only, instead of globally:

<template>
  <div class="container">

    <div>
        <dev-widget data-username="saurabhdaware"></dev-widget>
    </div>

  </div>
</template>

<script>

export default {
  layout: "default",

  head: {
    script: [
      {
        type: 'module',
        src: 'https://unpkg.com/[email protected]/dist/card.component.mjs'
      }
    ]
  },

};
</script>

Figured it out. Here's the complete solution that would work if we want to include a js (or mjs) file in ONE nuxt.js page only, instead of globally:

<template>
  <div class="container">

    <div>
        <dev-widget data-username="saurabhdaware"></dev-widget>
    </div>

  </div>
</template>

<script>

export default {
  layout: "default",

  head: {
    script: [
      {
        type: 'module',
        src: 'https://unpkg.com/[email protected]/dist/card.component.mjs'
      }
    ]
  },

};
</script>

I have nested comments in my Laravel app (the Comment model is polymorphic, ie, the comment can be made to either a Post, or a Comment, as you can see in the example below with commentable_id and commentable_type)

id  comment_hash    commentable_type    commentable_id
17  cmpipb0         App\Comment         15
15  cmpipap         App\Comment         11
11  cmpipa4         App\Comment         1
1   cmpip7n         App\Post            5

Note: I am not storing (nor do I intend to store) the post_id as a separate field with each comment in the comments table.

Given a comment (#17 in this example), I need to recursively go "up" the tree of comments until I get the id / details of the Post that the comment belongs to. In the above example, we will be given the comment_hash "cmpipb0" for comment id#17, and I would like to get the Post with id "5", which is at the top of the tree of nested comments.

comment id #17 (with comment_hash: cmpipb0) which is the child of:
comment id #15 (with comment_hash: cmpipap) which is the child of:
comment id #11 (with comment_hash: cmpipa4) which is the child of:
comment id #1  (with comment_hash: cmpip7n) which is the child of:
post id #5

My Model code is below:

Comment.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Comment extends Model
{
    use SoftDeletes;

    public $table = 'comments';

    protected $dates = [
        'created_at',
        'updated_at',
        'deleted_at',
    ];

    protected $fillable = [
        'commentable_id',
        'commentable_type',
        'body',
        'user_id',
        'created_at',
        'updated_at',
        'deleted_at',
        'comment_hash',
    ];

    /**
     * Customize the primary key for comment model.
     * Enabling this results in 'out of memory'. See https://laracasts.com/discuss/channels/eloquent/changing-primary-key-of-a-polymorphic-comments-table-from-id-to-a-varchar-php-runs-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');
    }

}

Post.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model implements HasMedia
{
    public $table = 'posts';

    protected $dates = [
        'created_at',
        'updated_at',
        'deleted_at',
    ];

    protected $fillable = [
        'slug',
        'body',
        'title',
        'user_id',
        'updated_at',
        'created_at',
        'deleted_at',
    ];

    /**
     * Get all of the post's comments.
     */
    public function comments()
    {
        return $this->morphMany(Comment::class, 'commentable');
    }

}

What is the best way to do this in Laravel?

I have a relatively simple question.

I am trying to implement the widget from this codepen in Nuxt.js.

Here's my code, which works fine if I use RAW HTML:

<!DOCTYPE html>
<html>
<head></head>
<body>
  <dev-widget data-username="saurabhdaware"></dev-widget>
  <script src="https://unpkg.com/[email protected]/dist/card.component.mjs" type="module"></script>
</body>

</html>

But when I try to include this dev widget in my nuxt.js project, in one of my pages, it does not work.

Here is my code:

<template>
  <div class="container">

    <div>
        <dev-widget data-username="saurabhdaware"></dev-widget>
    </div>

  </div>
</template>

<script>

export default {
  layout: "default",
};
</script>

<script src="https://unpkg.com/[email protected]/dist/card.component.mjs" type="module"></script>

I keep getting an error:

Unknown custom element: < dev-widget >

Any idea what I am doing wrong here?

@fylzero @jlrdw @lelectrolux @rodrigo.pedra thank you so much for sharing your thoughts...

I spent a lot of time thinking about all your answers and experimenting....but I really wanted a front-end solution (to save the burden of having to maintain separate counts on the back-end side).

There's no denying that @rodrigo.pedra 's answer nailed it! Thank you!

Highly recommend that you look into nuxt.js - it takes a lot of the pain you're dealing with away. I personally am using nuxt (which is a vue.js framework) with laravel as a backend and could not be happier.

@fylzero I'm trying to do it when the components are initialized instead of using VueX.

• Child initialized (mounted hook) = increment counter and bubble up to the parent.
• Parent =increment it's own counter and bubble up to it's parent recursively..
• and so on.

I haven't been able to get it to work though.

I am trying to implement nested comments in vue.js and nuxt.js.

• Each comment can have one or more children comments.
• Each child comment, can again, have one or more children comments.
• Unlimited levels of nested comments is possible.

As you can see in the diagram I have attached, I would like each comment to "know" (for the sake of simplicity, to display) the following information:

1. The depth of the comment (I have this working already). Example, all of the "top-level" comments are at depth=0, all their children are at depth=1, and so on.
2. The number of direct children
3. the number of children (including nested children, unlimited levels deep)

I came across this question on StackOverflow but it doesn't quite do the trick. Or maybe I am doing something wrong.

In case you want to take a look at my (very messy) code, here it is. However, I'm willing to start over, so appreciate any pointers on how to pass the data up / down the chain of nested comments (vue components). Some sample code would be great.

components/PostComment.vue:

<template>
<div>


  <div class="tw-flex tw-flex-wrap tw-justify-end">
    <div :class="indent" class="tw-w-full tw-flex">

      <div class="tw-font-bold tw-p-4 tw-border-gray-400 tw-border tw-rounded tw-text-right">
        <div class="kb-card-section">
          <div class="kb-card-section-content tw-flex tw-flex-wrap tw-items-center tw-text-left">

            <div class="tw-flex tw-w-full">
              <div class="tw-hidden md:tw-block md:tw-w-2/12 tw-text-right tw-my-auto">
                <div class="tw-flex">
                  <p class="tw-w-full tw-text-xs tw-text-gray-600 tw-text-right">children: {{ numNestedChildComments }}, depth: {{depth}}</p>
                </div>
              </div>
            </div>

          </div>

        </div>

      </div>

    </div>

    <div class="tw-w-full" v-if="commentData.nested_comments" v-for="nestedComment in commentData.nested_comments">
      <post-comment 
        :commentData="nestedComment"
        :depth="depth + 1"
        :numChildCommentsOfParent=numNestedChildComments
      />
    </div>

  </div>


</div>
</template>

<script>

export default {
  name: 'post-comment', // necessary for recursive components / nested comments to work
  props: {
    depth: {
      type: Number,
      required: true
    },
    postAuthorData: {
      type: Object,
      required: true
    },
    commentAuthorData: {
      type: Object,
      required: true
    },
    commentData: {
      type: Object,
      required: true
    },
    numChildCommentsOfParent: {
      type: Number,
      required: true
    },
  },
  data() {
    return {
      numNestedChildComments: this.numChildCommentsOfParent,
    }
  },
  mounted() {
    this.incrementNumParentComments();
  },
  methods: {
    incrementNumParentComments() {
      this.numNestedChildComments++;
      this.$emit('incrementNumParentComments');
    },
  },
  computed: {
    indent() {
      switch (this.depth) {
        case 0:
          return "tw-ml-0 tw-mt-1";
        case 1:
          return "tw-ml-4 tw-mt-1";
        case 2:
          return "tw-ml-8 tw-mt-1";
        case 3:
        default:
          return "tw-ml-12 tw-mt-1";
      }
    },
  },
}

</script>

Hi folks! 👋👋👋

Some questions for all you performance aficionados and AWS / Cloud experts out there.

I'm looking for a cheap (as close to free as possible) service for:

1. Hosting AND serving images. These images will be used on a website, in emails, etc. I want to plan for:

• 100GB of added storage / month
• 100M image views (GET requests) / month
• 100K new image uploads (PUT / POST requests) / month

2. CDN / Edge caching - so as to serve requests as fast as possible. Here I am looking to reduce response times and website load times that end-users will experience.

AWS both has an amazing suite of products and at the same time is very difficult to get started with.AWS S3's pricing model is confusing. I did also play a bit with their calculator, but it's hard to say if I'm entering the numbers in correctly.

Q1: In the AWS ecosystem:

• For S3: What is "Storage pricing" vs "Request Pricing"?
• What is S3 Select and how is it different from S3?
• What is S3 Intelligent-Tiering?
• What is S3 Glacier?
• And what about Amazon CloudFront?

Q2: Is AWS the best (and cheapest) available option? What about services like:

• Cloudflare
• Cloudinary
• Photon by Jetpack etc?
• Versus using my Linode server itself for hosting and serving images?
• versus the 1000+ other options out there?

Thoughts on what service I should be using? Looking for advice from folks that are knowledgeable on the matter. 🙏🙏🙏

Also looks like Chrome's the only browser being supported.

Boom...thanks @bobbybouwmann I just needed to know what to Google for. It's funny, I still can't find their official docs.

I have regular "Login with Google" oAuth implemented (screenshot 1 below...you know, where you click a link, and are taken to a page or modal with a "Sign up with Google" button)

But I would like to do what Redfin is doing (Screenshot 2 below). How does Redfin auto-detect all of my Google accounts and show a tiny box at the top right as soon as I go to redfin.com? Is there an API or some way to do this?

@vincej perhaps if you can put up your project (or bits thereof) online - github ideally, others can help debug or at least replicate the problem.

@jlrdw @sinnbeck I got it working on MySQL 8. Thank you both for your help..

It was painful but this is how I fixed it

Debugging:

$mysql --verbose --help | grep my.cnf

$ which mysql
/usr/local/bin/mysql

Resolution:

nano /usr/local/etc/my.cnf

Add: default-authentication-plugin=mysql_native_password

-------
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
default-authentication-plugin=mysql_native_password
------

Finally Run: brew services restart mysql

Yes, and again, thank you both for caring to respond.

@jlrdw @sinnbeck I got it working on MySQL 8. Thank you both for your help..

It was painful but this is how I fixed it

Debugging:

$mysql --verbose --help | grep my.cnf

$ which mysql
/usr/local/bin/mysql

Resolution:

nano /usr/local/etc/my.cnf

Add: default-authentication-plugin=mysql_native_password

-------
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
default-authentication-plugin=mysql_native_password
------

Finally Run: brew services restart mysql

@jlrdw thanks for the response. The query does work if I run it manually.

I did find this I'm trying to do this right now, so I dont quite have it working yet.

@jlrdw shouldn't it work with any data? I just tried without faker and same error

   Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: insert into `users` (`email`, `name`, `password`) values ([email protected]$

  at /Users/kp/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:665
    661|         // If an exception occurs when attempting to run a query, we'll format the error
    662|         // message to include the bindings with SQL, which will make this exception a
    663|         // lot more helpful to the developer instead of just the database's errors.
    664|         catch (Exception $e) {
  > 665|             throw new QueryException(
    666|                 $query, $this->prepareBindings($bindings), $e
    667|             );
    668|         }
    669|

  Exception trace:

  1   PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")
      /Users/kp/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=__connecteev_laravel_email_campaigns_and_tracking_example", "testuser", "testuser", [])
      /Users/kp/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

  Please use the argument -v to see more details.

I'm just really puzzled by this.

@sinnbeck thanks...yeah I did run the command with localhost and reloaded with FLUSH PRIVILEGES

I set up a test user for MySQL:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testuser';
ALTER USER 'testuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'testuser'; FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'localhost';

I changed my db credentials in my .env and confirmed that Laravel is able to connect to the DB. However, the cron still fails :(

@sinnbeck thank you. What did you mean to say when you started with "Cron looks like it should..."

Yes, I am using root as the username...so it is consistent.

@sinnbeck

This is my crontab -e:

* * * * * cd /Users/kp/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example && php artisan schedule:run >> /Users/kp/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example/cron.out

Also, just checked. The users are the same in both cases.

I verified using "whoami" before php artisan schedule:run and also added this to the cron job:

            $schedule->exec('whoami')
            ->everyMinute()
            ->before(function () {
                echo "Task is about to start...\n";
            })
            ->after(function () {
                echo "Task is complete...\n";
            })
            ->onSuccess(function () {
                echo "The task succeeded...\n";
            })
            ->onFailure(function () {
                echo "The task failed...\n";
            })
            ->appendOutputTo('whoami_logs.txt')

And whoamilogs.txt shows the same username as when I run php artisan schedule:run manually.

Thanks @jlrdw but it didn't work.

I tried adding

            'modes'       => [
                'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ],

to my database.php

Also tried this

#DB_HOST=127.0.0.1
DB_HOST=localhost

in my .env and restarted laravel

Also tried restarting MySQL. Nothing worked.

I am seeing a rather strange issue when running a Laravel Command. I am trying to do a simple insert into MySQL 8 with a cron command.

In app/Console/Kernel.php:

<?php

namespace App\Console;

use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

use Illuminate\Support\Facades\DB;
use Faker\Generator as Faker;

class Kernel extends ConsoleKernel
{
    /**
     * The Artisan commands provided by your application.
     *
     * @var array
     */
    protected $commands = [
        //
    ];

    protected function schedule(Schedule $schedule)
    {
        $schedule->call(function (Faker $faker) {
            DB::table('users')->insert(
                [
                    'email' => $faker->unique()->safeEmail,
                    'name' => $faker->username,
                    'password' => $faker->password,
                ]
            );
        })->everyMinute();
    }

If I execute php artisan schedule:run from the command line, everything works beautifully, and a new entry gets added to the users table.

However, my cron job (which is configured correctly and is able to run other commands) errors out:

Running scheduled command: Closure

   Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: insert into `users` (`email`, `name`, `password`) values (nolan.maxi$

  at /Users/kunalpunjabi/Code/EXPLORATIONS/EMAILS_QUEUES_CACHING_REDIS/connecteev_laravel_email_campaigns_and_tracking_example/vendor/laravel/framework/src/Illuminate/Database/Connection.php:665
    661|         // If an exception occurs when attempting to run a query, we'll format the error
    662|         // message to include the bindings with SQL, which will make this exception a
    663|         // lot more helpful to the developer instead of just the database's errors.
    664|         catch (Exception $e) {
  > 665|             throw new QueryException(
    666|                 $query, $this->prepareBindings($bindings), $e
    667|             );
    668|         }
    669|

  Exception trace:

  1   App\Console\Kernel::App\Console\{closure}(Object(Faker\Generator))
      [internal]:0

  2   App\Console\Kernel::App\Console\{closure}(Object(Faker\Generator))
      [internal]:0

  Please use the argument -v to see more details.

Note: As per this recommendation, I already tried this:

        // On MySQL 8.0, may need to run: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; FLUSH PRIVILEGES; // replace 'root' with MySQL password

But it did not help. I am seeing the same error.

Any advice on what the problem might be?

It's similar...thanks. I marked your answer the best answer.

Thanks but that didn't quite work.

Solved it by doing this.

        // Validate the request with the rules() set in StorePostRequest.php
        $storePostRequest = new StorePostRequest();
        $validator = Validator::make($requestData, $storePostRequest->rules());
        if ($validator->fails()) {
            return response($validator->errors(), Response::HTTP_BAD_REQUEST);
        }
        $post = Post::create($requestData);

I have an API resource that accepts a request as such:

 public function create(StorePostRequest $request) {
 }

The problem is, when I send a POST request to this create() endpoint, the rules() defined in StorePostRequest.php kick in before the method is even called. I want to do some pre-validation and THEN validate with the rules() in the StorePostRequest.

The rules() that are defined in StorePostRequest.php kick in before the create() method is even called.

    public function rules()
    {
        return [
            'title'                  => [
                'min:10',
                'max:255',
                'required',
            ],
            'slug'                   => [
                'min:10',
                'max:255',
                'required',
                // 'unique:posts',
            ],
            'user_id'                => [
                'required',
                'integer',
            ],
        ];

For example, the user_id should be retrieved from the Auth() session, and not get passed in. But I do want the rule to be there when the data gets stored to the posts table.

So, I changed the API to:

    // Note: if you accept a StorePostRequest $request, it automatically pre-validates based on rules() in StorePostRequest
    public function create(Request $request)
    {
        $loggedinUser = auth()->user();
        if (!$loggedinUser) {
            return response(["error" => "User is not logged in. User needs to be logged in to use this API."], Response::HTTP_FORBIDDEN);
        }
        $loggedinUserId = $loggedinUser->id;

        $requestData = $request->all();
        if (!isset($requestData['title'])) {
            return response(["error" => "title is required"], Response::HTTP_BAD_REQUEST);
        }
        if (!isset($requestData['body'])) {
            return response(["error" => "body is required"], Response::HTTP_BAD_REQUEST);
        }
        if (!isset($requestData['status'])) {
            return response(["error" => "status is required"], Response::HTTP_BAD_REQUEST);
        }
        $requestData['slug'] = $this->createUniquePostSlug($request->title);
        $requestData['user_id'] = $loggedinUserId;
        $requestData['description'] = $request->title;
        $requestData['reading_time_minutes'] = ceil(str_word_count(strip_tags($requestData['body'])) / 200); // assuming an average reading speed of 200 wpm

        $post = Post::create($requestData);

        return (new PostResource($post))
            ->response()
            ->setStatusCode(Response::HTTP_CREATED);
    }

However now I have to do a bunch of manual validation (using isset($requestData) like I'm doing above. I want the StorePostRequest rules to kick in, but after I do some request validation myself. Is there a way to do this? Maybe there's a way to cast the Request object to the StorePostRequest? THoughts? And thanks in advance.

@gamerfac3 I've already read those docs, this is what I am doing to request the appropriate scopes:

        switch($service) {
            case 'github':
                // https://developer.github.com/apps/building-oauth-apps/understanding-scopes-for-oauth-apps/
                $scopesArray = ['user:email', 'read:user']; // Socialite default: ['user:email']
                break;
            case 'google':
                // https://developers.google.com/identity/protocols/googlescopes#google_sign-in
                $scopesArray = ['openid', 'profile', 'email']; // Socialite default: ['openid', 'profile', 'email']
                break;
            case 'linkedin':
                // https://docs.microsoft.com/en-us/linkedin/consumer/integrations/self-serve/sign-in-with-linkedin#authenticating-members
                $scopesArray = ['r_liteprofile', 'r_emailaddress']; // Socialite default: ['r_liteprofile', 'r_emailaddress']
                break;
            default:
                return response(['error' => 'Yo! Invalid service, ' . $service . ' is not supported.'], Response::HTTP_BAD_REQUEST);
                break;
        }
        Socialite::driver($service)->setScopes($scopesArray);

The problem is I don't get a username / nickname back for LinkedIn or Google:

            $socialiteUser = Socialite::driver($service)->userFromToken($request->providerToken);
            $socialUsername = $socialiteUser->getNickname();

It comes back as NULL, eventhough I have usernames for both these services.

I'm using the latest version of Laravel Socialite to log a user into:

• Linkedin
• Google

How can I request extended data to be returned back from any / all of these services? For example: In the case of Linkedin, I am interested in the Linkedin profile data that includes:

1. Location
2. Current TItle
3. Current Company etc In the case of Google, I am interested in:
4. Gender
5. Birthday
6. Location

These are the authorization URLs I request at the start of my oAuth flow.

    // Google:  https://accounts.google.com/signin/oauth?client_id=833332220512-u1meuuiposp1gdk8uh80hn27dh41qkpa.apps.googleusercontent.com&as=wxbImJ-i9ngw6UZft-Y9Yw&destination=http://localhost:8000&approval_state=!ChRhZjRx3kVTNDVnbU1pTG5ucnhFUBIfTTNqTmItU2dnQ01Ta013TWpqbVNUV1Z5NFcxcTNoWQ%E2%88%99AJDr988AAAAAXa0BVq86VO6bEvcHskT34vAim0aj9mSj&oauthgdpr=1&xsrfsig=ChkAeAh8T9wlk22s0o1wZ_EL4vgTgDUpAfEEg5hcHByb3ZhbF9zdGF0ZRILZGVzdGluYXRpb24SBXNvYWN1Eg9vYXV0aHJpc2t5c2NvcGU
    // Linkein: https://www.linkedin.com/oauth/v2/authorization?client_id=36522h97mc5oew&redirect_uri=http://localhost:8000/api/v1/auth/login/linkedin/callback&response_type=code&scope=r_liteprofile%20r_emailaddress%20w_member_social

I've already read the docs, this is what I am doing to request the appropriate scopes:

        switch($service) {
            case 'github':
                // https://developer.github.com/apps/building-oauth-apps/understanding-scopes-for-oauth-apps/
                $scopesArray = ['user:email', 'read:user']; // Socialite default: ['user:email']
                break;
            case 'google':
                // https://developers.google.com/identity/protocols/googlescopes#google_sign-in
                $scopesArray = ['openid', 'profile', 'email']; // Socialite default: ['openid', 'profile', 'email']
                break;
            case 'linkedin':
                // https://docs.microsoft.com/en-us/linkedin/consumer/integrations/self-serve/sign-in-with-linkedin#authenticating-members
                $scopesArray = ['r_liteprofile', 'r_emailaddress']; // Socialite default: ['r_liteprofile', 'r_emailaddress']
                break;
            default:
                return response(['error' => 'Yo! Invalid service, ' . $service . ' is not supported.'], Response::HTTP_BAD_REQUEST);
                break;
        }
        Socialite::driver($service)->setScopes($scopesArray);

Is what I'm trying possible, and if so, how can I get this additional data back?

I'm using the latest version of Laravel Socialite to log a user into:

• Github
• Google
• Linkedin

The problem I am having is that I am able to get the basic user details (email, id) but NOT the user's nickname / username.

            $socialiteUser = Socialite::driver($service)->userFromToken($request->providerToken);
            $socialUsername = $socialiteUser->getNickname();

This works fine for Github but I get NOTHING back for Google and Linkedin, so $socialUsername ends up being null. I am wondering if this is a scope issue?

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?