rodrigo.pedra

Lead Developer at avaliadora.com.br

Member Since 6 Years Ago

São Carlos, Brazil

Experience Points
381,930
Total
Experience

0 experience to go until the next level!

In case you were wondering, you earn Laracasts experience when you:

  • Complete a lesson — 100pts
  • Create a forum thread — 50pts
  • Reply to a thread — 10pts
  • Leave a reply that is liked — 50pts
  • Receive a "Best Reply" award — 500pts
Lessons Completed
1144
Lessons
Completed
Best Reply Awards
402
Best Reply
Awards
  • start your engines Created with Sketch.

    Start Your Engines

    Earned once you have completed your first Laracasts lesson.

  • first-thousand Created with Sketch.

    First Thousand

    Earned once you have earned your first 1000 experience points.

  • 1-year Created with Sketch.

    One Year Member

    Earned when you have been with Laracasts for 1 year.

  • 2-years Created with Sketch.

    Two Year Member

    Earned when you have been with Laracasts for 2 years.

  • 3-years Created with Sketch.

    Three Year Member

    Earned when you have been with Laracasts for 3 years.

  • 4-years Created with Sketch.

    Four Year Member

    Earned when you have been with Laracasts for 4 years.

  • 5-years Created with Sketch.

    Five Year Member

    Earned when you have been with Laracasts for 5 years.

  • school-in-session Created with Sketch.

    School In Session

    Earned when at least one Laracasts series has been fully completed.

  • welcome-newcomer Created with Sketch.

    Welcome To The Community

    Earned after your first post on the Laracasts forum.

  • full-time-student Created with Sketch.

    Full Time Learner

    Earned once 100 Laracasts lessons have been completed.

  • pay-it-forward Created with Sketch.

    Pay It Forward

    Earned once you receive your first "Best Reply" award on the Laracasts forum.

  • subscriber Created with Sketch.

    Subscriber

    Earned if you are a paying Laracasts subscriber.

  • lifer Created with Sketch.

    Lifer

    Earned if you have a lifetime subscription to Laracasts.

  • evangelist Created with Sketch.

    Laracasts Evangelist

    Earned if you share a link to Laracasts on social media. Please email [email protected] with your username and post URL to be awarded this badge.

  • chatty-cathy Created with Sketch.

    Chatty Cathy

    Earned once you have achieved 500 forum replies.

  • lara-veteran Created with Sketch.

    Laracasts Veteran

    Earned once your experience points passes 100,000.

  • 10k-strong Created with Sketch.

    Ten Thousand Strong

    Earned once your experience points hits 10,000.

  • lara-master Created with Sketch.

    Laracasts Master

    Earned once 1000 Laracasts lessons have been completed.

  • laracasts-tutor Created with Sketch.

    Laracasts Tutor

    Earned once your "Best Reply" award count is 100 or more.

  • laracasts-sensei Created with Sketch.

    Laracasts Sensei

    Earned once your experience points passes 1 million.

  • top-50 Created with Sketch.

    Top 50

    Earned once your experience points ranks in the top 50 of all Laracasts users.

  • Community Pillar

    Earned once your experience points ranks in the top 10 of all Laracasts users.

Level 50
381,930 XP
Mar
02
4 days ago
Activity icon

Awarded Best Reply on How To Update And Insert Database Data When Releasing A New Application's Version?

I could just create a migration to update the data without changing the structure

If this records are required to be present for this new release, maybe some global settings or similar, I see no problem using migrations to ensure the new version has them.

I would use them if that is the case.

Feb
28
6 days ago
Activity icon

Awarded Best Reply on Loop Row In Bootstrap Every 3 Columns

col-4 will already wrap around every 3 columns. As Bootstrap 4 uses flex instead of floats for rows, there will be always 3 divs per row.

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">

<div class="row">
    @foreach(range(1, 12) as $record)
        <div class="col-4">{{ $record }}</div>
    @endforeach
</div>

If you really want to manually add a .row div every 3 elements, you can use blade's $loop variable:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">

@foreach(range(1, 12) as $record)
    @if($loop->index % 3 === 0)
        <div class="row">
    @endif

        <div class="col-4">{{ $record }}</div>

    @if($loop->index % 3 === 0)
        </div>
    @endif
@endforeach

https://laravel.com/docs/8.x/blade#the-loop-variable

Feb
26
1 week ago
Activity icon

Awarded Best Reply on Trying To Get Logic Out Of Blade Nested Relationship

You can name the $relation variable anyway you prefer to. I like to name it $relation as if you die and dump it (dd()) you'll see that in that case it will be a HasMany instance.

It comes from applying constraints to the eager loaded relation. You can read more about this on the docs:

https://laravel.com/docs/8.x/eloquent-relationships#constraining-eager-loads

You can see in the docs they name the closure parameter as $query. As I said before, I like to name it $relation because I find it more meaningful.

The online documentation is very good but I would not be able to derive the query you came up with from it. Do you have any recommendations?

Most of my knowledge comes from: Reading the docs frequently, reading merged PR on Laravel's GitHub repo, and doing a lot of projects using Laravel (have been using it for 6+ years), reading and contributing on Laracasts forums, and watching a lot of Laracasts series.

Laracasts is not the only learning resource I use to learn more about Laravel, but it is from far the one I use the most and been more recurring. I don't feel like advertising competing learning resources in the forums (well, I did it once, but forum member who asked for didn't speak English and was looking for a resource in Spanish, so I didn't see a problem).

There are some blogs that also helped me a lot throughout the years:

From forum member @JarekTkaczyk . Not very active in Laravel these days as the author moved to another stack, but helped me a lot in my early days using Laravel and content is still very insightful and helpful.

From Jonathan Reinink, author of Inertia JS, long-time Laravel contributor, and frequent Laracon speaker.

From Jonathan Reinink, author of Inertia JS, long-time Laravel contributor, and frequent Laracon speaker.

From Tim MacDonald, frequent Laracon speaker.

From forum user @Snapey . From all this blogs is the only one I keep pinned on my browser.

There are other one-off articles I keep on my bookmarks, and also I recommend watching past Laracon talks. A lot of them are available for free on YouTube.

Back to your problem.

As you only want the last training for each user, you could add a a lastTraining relation to the User model that uses a HasOne relation instead of a HasMany:

User Model

// No problem keeping this relation
public function trainings()
{
    return $this->hasMany('App\Training');
}

// Add this one
public function lastTraining()
{
    return $this->hasOne('App\Training')->latest();
}

This should do the trick, but there is a gotcha I will talk below.

In your query you would need to change your eager loading to:

$division = Division::query()
    ->with([
        'requirements',
        'users.lastTraining' => function ($relation) use ($id) {
            $relation->whereHas('requirements.divisions', function ($query) use ($id) {
                $query->whereKey($id);
            });
        },
    ])
    ->find($id);

As I already said, this should do the trick.

But unfortunately eager loading HasOne relations like this is not very efficient out of the box in Laravel, as it will fetch all related models and restrict to one for each parent when matching the query results.

So if your users have an average of 10 related queries trainings, and you have around 50 users, eager loading the lastTraining relation will fetch all 500 records from the database, hydrate the models, and then discard 450 of them when matching the results to the related users.

To mitigate that, and fetch only the records you want from the database there are some options:

1 - Use Dynamic Relationships

Jonathan Reinink from one of the blogs I listed above, has a blog post about it:

https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

The idea is to use subqueries on the SELECT to fetch just the related fields, and then eager load by it.

Although I prefer the next option I will talk below, I've reached for this solution and the blog post is worth reading.

2 - Use a dedicated package for this

I like to use the package below for this kind of situation: eager-loading HasOne or BelongsTo relations fetching from database just the records I need.

https://github.com/staudenmeir/eloquent-eager-limit

This package is maintained by Jonas Staudenmeir, a long-time Laravel contributor specialized in the Database component.

He tried merging the feature provided by this package onto the core, but at the time the core maintainers deemed it as a niche-feature. So he released it as a package.

Take a good read on its README to understand better how it works, but in short, after installing it you'll need to do these changes on your models:

User Model

class User extends Authenticatable
{
    // need to add this trait
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
    
    // ... other code

    // No problem keeping this relation
    public function trainings()
    {
        return $this->hasMany('App\Training');
    }

    // Add this one
    public function lastTraining()
    {
        return $this->hasOne('App\Training')
            ->latest('id') // <<< added 'id' here, as it is an indexed column
            ->limit(1); // <<< need to add this for the package to work as expected
    }
}

Training Model

class Training extends Model
{
    // need to add this trait here too
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

    // ... other code
}

The query will be the same as my last snippet, but know only one record for each user will be retrieved.

Hope this helps.

Activity icon

Replied to Trying To Get Logic Out Of Blade Nested Relationship

You can name the $relation variable anyway you prefer to. I like to name it $relation as if you die and dump it (dd()) you'll see that in that case it will be a HasMany instance.

It comes from applying constraints to the eager loaded relation. You can read more about this on the docs:

https://laravel.com/docs/8.x/eloquent-relationships#constraining-eager-loads

You can see in the docs they name the closure parameter as $query. As I said before, I like to name it $relation because I find it more meaningful.

The online documentation is very good but I would not be able to derive the query you came up with from it. Do you have any recommendations?

Most of my knowledge comes from: Reading the docs frequently, reading merged PR on Laravel's GitHub repo, and doing a lot of projects using Laravel (have been using it for 6+ years), reading and contributing on Laracasts forums, and watching a lot of Laracasts series.

Laracasts is not the only learning resource I use to learn more about Laravel, but it is from far the one I use the most and been more recurring. I don't feel like advertising competing learning resources in the forums (well, I did it once, but forum member who asked for didn't speak English and was looking for a resource in Spanish, so I didn't see a problem).

There are some blogs that also helped me a lot throughout the years:

From forum member @JarekTkaczyk . Not very active in Laravel these days as the author moved to another stack, but helped me a lot in my early days using Laravel and content is still very insightful and helpful.

From Jonathan Reinink, author of Inertia JS, long-time Laravel contributor, and frequent Laracon speaker.

From Jonathan Reinink, author of Inertia JS, long-time Laravel contributor, and frequent Laracon speaker.

From Tim MacDonald, frequent Laracon speaker.

From forum user @Snapey . From all this blogs is the only one I keep pinned on my browser.

There are other one-off articles I keep on my bookmarks, and also I recommend watching past Laracon talks. A lot of them are available for free on YouTube.

Back to your problem.

As you only want the last training for each user, you could add a a lastTraining relation to the User model that uses a HasOne relation instead of a HasMany:

User Model

// No problem keeping this relation
public function trainings()
{
    return $this->hasMany('App\Training');
}

// Add this one
public function lastTraining()
{
    return $this->hasOne('App\Training')->latest();
}

This should do the trick, but there is a gotcha I will talk below.

In your query you would need to change your eager loading to:

$division = Division::query()
    ->with([
        'requirements',
        'users.lastTraining' => function ($relation) use ($id) {
            $relation->whereHas('requirements.divisions', function ($query) use ($id) {
                $query->whereKey($id);
            });
        },
    ])
    ->find($id);

As I already said, this should do the trick.

But unfortunately eager loading HasOne relations like this is not very efficient out of the box in Laravel, as it will fetch all related models and restrict to one for each parent when matching the query results.

So if your users have an average of 10 related queries trainings, and you have around 50 users, eager loading the lastTraining relation will fetch all 500 records from the database, hydrate the models, and then discard 450 of them when matching the results to the related users.

To mitigate that, and fetch only the records you want from the database there are some options:

1 - Use Dynamic Relationships

Jonathan Reinink from one of the blogs I listed above, has a blog post about it:

https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

The idea is to use subqueries on the SELECT to fetch just the related fields, and then eager load by it.

Although I prefer the next option I will talk below, I've reached for this solution and the blog post is worth reading.

2 - Use a dedicated package for this

I like to use the package below for this kind of situation: eager-loading HasOne or BelongsTo relations fetching from database just the records I need.

https://github.com/staudenmeir/eloquent-eager-limit

This package is maintained by Jonas Staudenmeir, a long-time Laravel contributor specialized in the Database component.

He tried merging the feature provided by this package onto the core, but at the time the core maintainers deemed it as a niche-feature. So he released it as a package.

Take a good read on its README to understand better how it works, but in short, after installing it you'll need to do these changes on your models:

User Model

class User extends Authenticatable
{
    // need to add this trait
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
    
    // ... other code

    // No problem keeping this relation
    public function trainings()
    {
        return $this->hasMany('App\Training');
    }

    // Add this one
    public function lastTraining()
    {
        return $this->hasOne('App\Training')
            ->latest('id') // <<< added 'id' here, as it is an indexed column
            ->limit(1); // <<< need to add this for the package to work as expected
    }
}

Training Model

class Training extends Model
{
    // need to add this trait here too
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

    // ... other code
}

The query will be the same as my last snippet, but know only one record for each user will be retrieved.

Hope this helps.

Activity icon

Awarded Best Reply on How To Customize Laravel Spatie Backup Notification Email ?

On this page the config file is listed:

https://spatie.be/docs/laravel-backup/v7/installation-and-setup

Close to the notifications key there is this comment:

    /*
     * You can get notified when specific events occur. Out of the box you can use 'mail' and 'slack'.
     * For Slack you need to install laravel/slack-notification-channel.
     *
     * You can also use your own notification classes, just make sure the class is named after one of
     * the `Spatie\Backup\Events` classes.
     */

So:

  • Install the package
  • Publish its config (installation show how)
  • Create a notification that extends from \Spatie\Backup\Notifications\Notifications\BackupWasSuccessfulNotification and name it the same on your local app's namespace
  • Update the config file replacing:
\Spatie\Backup\Notifications\Notifications\BackupWasSuccessfulNotification::class => ['mail'],

With:

\App\Notifications\BackupWasSuccessfulNotification::class => ['mail'],

I am assuming you will create the notification within the default path for notifications.

On your custom notification you can format it the way you want/need.

Activity icon

Replied to MySQL Or Eloquent

Actually if look into the fromQuery implementation I linked above, it is very similar:

    public function fromQuery($query, $bindings = [])
    {
        return $this->hydrate(
            $this->query->getConnection()->select($query, $bindings)
        );
    }

If just hides the PDO select and the hydrate call into one method.

But it very handy.

Activity icon

Replied to MySQL Or Eloquent

Hey @tovisbratsburg ,

A lesser known method from the Eloquent query builder is the fromQuery()

https://github.com/laravel/framework/blob/5f75dea32bae39f03aed82d6986026fcd4ca7d61/src/Illuminate/Database/Eloquent/Builder.php#L349-L361

You could write your raw SQL select, never tried with the output from a stored procedure, and them hydrate the models to have benefits such as type-casting, lazy-eager loading related models, etc.

// select query that is easier to write in SQL
// for example one that uses window functions, etc.
$sql = 'SELECT table_1.*, table_2.* FROM ...';  

// you can bind values to prevent SQL injection
$records = Report::fromQuery($sql, [$id, $fromDate, $toDate, ...]); 

dd($records); // $records will be a collection

I use this a lot for reports where it is easier to write in SQL while I still get some of benefits listed above and listed by @fylzero 's answer.

You can even define dynamic casting:

$records = Report::withCasts([
    'total' => 'decimal:2',
    // ...
])->fromQuery($sql, [$id, $fromDate, $toDate, ...]); 

In case you don't want to configure a model to every query you run.

Hope this helps.

Activity icon

Replied to Resources Is Not Showing An Array Of Result

You're welcome! Have a nice day =)

Activity icon

Replied to Trying To Get Logic Out Of Blade Nested Relationship

Not sure I totally got it, but try this:

$division = Division::query()
    ->with([
        'requirements',
        'users.trainings' => function ($relation) use ($id) {
            $relation->whereHas('requirements.divisions', function ($query) use ($id) {
                $query->whereKey($id);
            });
        },
    ])
    ->find($id);

I am assuming the Requirement model has a belongsToMany relation back to Division.

Also I removed the order by as you are getting one division by its ID.

Activity icon

Awarded Best Reply on Resources Is Not Showing An Array Of Result

Try these:

In your service class:

public function create(array $data = []): array
{
    $results = [];

    foreach ($data as $option) {
        $results[] = VariationOption::create([
            'type_id' => $option['type_id'],
            'value' => $option['value'],

        ]);
    }

    return $results;
}

before you were returning just the last created model

And in your controller:

public function store(VariationOptionCreateRequest $request)
{
    $options = $this->VariationOptionService->create($request->all());
    
    return response()->json(VariationOptionResource::collection($options));
}

Hope this helps

Activity icon

Replied to Resources Is Not Showing An Array Of Result

Try these:

In your service class:

public function create(array $data = []): array
{
    $results = [];

    foreach ($data as $option) {
        $results[] = VariationOption::create([
            'type_id' => $option['type_id'],
            'value' => $option['value'],

        ]);
    }

    return $results;
}

before you were returning just the last created model

And in your controller:

public function store(VariationOptionCreateRequest $request)
{
    $options = $this->VariationOptionService->create($request->all());
    
    return response()->json(VariationOptionResource::collection($options));
}

Hope this helps

Activity icon

Replied to Why Header Is Not Working ??

This thread on Stack Overflow has more details and some alternatives using JavaScript.

https://stackoverflow.com/a/822732

A quote from the accepted answer that might also give you an alternative:

The common thing (e.g. in popular download sites) is the reverse: first you go to the after page and then the download starts.

Activity icon

Replied to Why Header Is Not Working ??

As ->download() adds attachment to the Content-Disposition header, the refresh header won't be processed by the browser process where the user is using your system.

The browser will download the file as it would be a separate "process"/"tab".

I don't know how you could do that (redirect after download ends) using only standard HTTP headers.

Feb
24
1 week ago
Activity icon

Awarded Best Reply on Curl -s Https://laravel.build/example-app | Bash

This article helped me install and configure WSL 2 on a Windows box earlier this month. Might help you out:

https://www.omgubuntu.co.uk/how-to-install-wsl2-on-windows-10

Activity icon

Replied to Curl -s Https://laravel.build/example-app | Bash

You're welcome.

I tried sail, but like you said am still using composer. In my case I am already 6 years working with Laravel, so old habits are hard to change.

But glad you got it working.

Activity icon

Awarded Best Reply on How To Login The User With Specific Guard Like Api In Laravel Passport

Try this:

auth()->guard('api')->setUser($user);

attempt() is only available to guards implementing the StatefulGuard interface.

Feb
23
1 week ago
Activity icon

Replied to Why Do I Get Two Warnings When Installing Npm On My Laravel Project?

It is ok.

It means fsevent is not supported on your operating system.

Laravel Mix uses this package to send notifications when a task ends. But this package does not work on all platforms, namely Windows.

It won't affect your assets compilation.

Activity icon

Replied to Curl -s Https://laravel.build/example-app | Bash

This article helped me install and configure WSL 2 on a Windows box earlier this month. Might help you out:

https://www.omgubuntu.co.uk/how-to-install-wsl2-on-windows-10

Activity icon

Replied to Curl -s Https://laravel.build/example-app | Bash

Shouldn't you be running those commands inside the WSL shell, not on PowerShell directly.

I think you can start PowerShell and move to a WSL shell inside it, but the commands should be running from the underlying Linux shell installed trough WSL.

From the snippet you pasted above it seems you are running the commands in PowerShell using the Windows filesystem instead.

Also, depending on the Linux distro you installed through WSL, cURL might not be installed by default.

If it is a debian/ubuntu based distro you can try running this command to install it:

$ sudo apt-get install curl

To ensure cURL is installed.

Activity icon

Replied to Create Csv File Then Upload To Remote SFTP (solved)

Some thoughts:

1 - Did you install the composer package required for SFTP?

https://laravel.com/docs/8.x/filesystem#driver-prerequisites

2 - You don't need to output headers as you are not sending the results to the browser, maybe that could be interfering.

Activity icon

Replied to Curl -s Https://laravel.build/example-app | Bash

Do you have WSL2 installed and configured on your Windows installation?

From Sails docs page:

Laravel Sail is supported on macOS, Linux, and Windows (via WSL2).

https://laravel.com/docs/8.x/sail

Try running this command from a terminal using the Windows Subsystem for Linux version 2.

As docs implies you should have WSL2 installed and with a Linux distro of your choice configured.

Activity icon

Replied to How To Update And Insert Database Data When Releasing A New Application's Version?

I could just create a migration to update the data without changing the structure

If this records are required to be present for this new release, maybe some global settings or similar, I see no problem using migrations to ensure the new version has them.

I would use them if that is the case.

Activity icon

Replied to How To Login The User With Specific Guard Like Api In Laravel Passport

Try this:

auth()->guard('api')->setUser($user);

attempt() is only available to guards implementing the StatefulGuard interface.

Activity icon

Replied to How To Set My Custom Password Broker To Be Used By Password::sendResetLink ?

The provider references a User Provider configured in the 'providers' key in the ./config/auth.php.

Password façade will use the 'passwords' driver configured in the 'defaults' key in the ./config/auth.php

In short you ./config/auth.php should look like this:

<?php

return [

    'defaults' => [
        'guard' => 'web',
        'passwords' => 'users:foo', // <<< CHANGED HERE
    ],

    'guards' => [
        // ... omitted for bevity
    ],

    'providers' => [
        'users' => [
            'driver' => 'eloquent',
            'model' => App\Models\User::class,
        ],

        'users:foo' => [ // <<< ADDED HERE, SEE NOTES
            'driver' => 'eloquent',
            'model' => App\Models\User::class,
        ],
    ],

    'passwords' => [
        'users' => [
            'provider' => 'users',
            'table' => 'password_resets',
            'expire' => 60,
            'throttle' => 60,
        ],

        'users:foo' => [ // <<< ADDED HERE, SEE NOTES
            'provider' => 'users:foo',
            'table' => 'foo.password_resets',
            'expire' => 60,
            'throttle' => 60,
        ],
    ],

    'password_timeout' => 10800,

];

Note: if you will use the default user table, with the default user model, you won't need a custom user provider, just a custom password driver, in that case you could simplify to this:

<?php

return [

    'defaults' => [
        'guard' => 'web',
        'passwords' => 'users:foo', // <<< CHANGED HERE
    ],

    'guards' => [
        // ... omitted for bevity
    ],

    'providers' => [
        'users' => [
            'driver' => 'eloquent',
            'model' => App\Models\User::class,
        ],
    ],

    'passwords' => [
        'users' => [
            'provider' => 'users',
            'table' => 'password_resets',
            'expire' => 60,
            'throttle' => 60,
        ],

        'users:foo' => [
            'provider' => 'users',  // <<< USES DEFAULT PROVIDER
            'table' => 'foo.password_resets',
            'expire' => 60,
            'throttle' => 60,
        ],
    ],

    'password_timeout' => 10800,

];

Hope this helps.

Activity icon

Replied to Join Two Queries At The Model Level

Thanks for reporting back @trin !

I wonder if using the WHERE IN ('id', (SELECT id FROM ...)) where we move the UNIONs to the subquery, so the order by is applied on the outer query would be the best of both worlds.

One tip: when writing a long answer where I need to paste stuff from other resources, I often type it first on another app and then I paste the final version to avoid losing it (It already happened to me in the past).

One standalone markdown editor I like to use is Typora (https://typora.io/)

Activity icon

Replied to Error Responses

You’re welcome! Have a nice day =)

Activity icon

Awarded Best Reply on Error Responses

You could customize your app's Exception Handler to normalize the error responses.

The docs outlines how to do it:

https://laravel.com/docs/8.x/errors#rendering-exceptions

You would add something like this:

$this->renderable(function (\Throwable $e, Request $request) {
    if (! $request->expectsJson()) {
        return null; // let Laravel handle it
    }

    if ($e instanceof ValidationException) {
        return null; // let Laravel handle it
    }

    if ($e instanceof AuthenticationException) {
        return null; // let Laravel handle it
    }

    $message = $this->isHttpException($e) ? $e->getMessage() : 'Server Error';
    $statusCode = $this->isHttpException($e) ? $e->getStatusCode() : 500;

    return response()->json([
        'message' => $message,
        'errors' => [],
    ], $statusCode);
});

Hope this helps.

Activity icon

Replied to Join Two Queries At The Model Level

You're welcome!

Please share your findings, I have a project that could use some performance improvements on a similar use case.

Activity icon

Replied to Laravel - Should Pipelines Replace Queries

You're welcome! Have a nice day =)

Activity icon

Replied to Join Two Queries At The Model Level

It would need more data to make a difference on the explains, as the query execution plan can change depending on the data.

I added more data to show that difference, but it is still a very small dataset. It is out of scope to add a large one here.

My point was that "unioning" the same table with simple WHERE clauses is about the same as using a OR condition.

Actually, from the explains above, when you add sorting it is worse depending on the size of the dataset.

You can see from the UNION query plan it will use a temporary table + filesort to sort the UNION resultset. In other words, there will be disk I/O operations just for sorting data from the same table.

Activity icon

Replied to Join Two Queries At The Model Level

One could argue they won't get all DONE in the first place.

Also in the first snippet always 2 queries will run, one to fetch the TrelloList and the other to fetch the unions.

Also depending if TrelloList model as global scope applying an order by (as it seems to be the case with the TrelloCard model), ->first() would change results if the order by changes.

As you asked about union performance (UPDATED with more data):

use `dummy`;

drop table if exists `trello_cards`;
drop table if exists `trello_lists`;

create table `trello_lists` (
  `id` bigint unsigned not null auto_increment,
  `name` varchar(255) not null,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  primary key(`id`)
);

insert into trello_lists (`name`, `created_at`, `updated_at`)
values ('DONE', NOW(), NOW()),
     ('OTHER', NOW(), NOW());

create table `trello_cards` (
  `id` bigint unsigned not null auto_increment,
  `list_id` bigint unsigned not null,
  `customer_id` bigint unsigned not null,
  `is_archived` bigint unsigned not null,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  primary key(`id`),
  CONSTRAINT `trello_cards_list_id_foreign` FOREIGN KEY (`list_id`)
      REFERENCES `trello_lists` (`id`) ON DELETE CASCADE
);

insert into `trello_cards` (`customer_id`, `list_id`, `is_archived`, `created_at`, `updated_at`)
values (1, 1, 1, NOW(), NOW()),
    (1, 1, 0, NOW(), NOW()),
    (2, 1, 1, NOW(), NOW()),
    (2, 1, 0, NOW(), NOW()),
    (3, 1, 1, NOW(), NOW()),
    (3, 1, 0, NOW(), NOW()),
    (4, 1, 1, NOW(), NOW()),
    (4, 1, 0, NOW(), NOW()),
    (1, 2, 1, NOW(), NOW()),
    (1, 2, 0, NOW(), NOW()),
    (2, 2, 1, NOW(), NOW()),
    (2, 2, 0, NOW(), NOW()),
    (3, 2, 1, NOW(), NOW()),
    (3, 2, 0, NOW(), NOW()),
    (4, 2, 1, NOW(), NOW()),
    (4, 2, 0, NOW(), NOW());

EXPLAIN
SELECT *
FROM `trello_cards`
WHERE (
    `is_archived` = 1
    OR `list_id` IN (SELECT `id` FROM `trello_lists` WHERE `name` = 'DONE')
)
    AND `customer_id` = 1
    AND `customer_id` IS NOT NULL
ORDER BY `created_at`;

EXPLAIN
SELECT *
FROM `trello_cards`
WHERE
    `is_archived` = 1
    AND `customer_id` = 1
    AND `customer_id` IS NOT NULL
UNION
SELECT *
FROM `trello_cards`
WHERE
    `is_archived` = 0
    AND `list_id` = 1 -- On the original snippet there is
        -- an additional query to fetch this ID
    AND `customer_id` = 1
    AND `customer_id` IS NOT NULL
ORDER BY `created_at`;

First explain (using WHERE IN):

[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "trello_cards",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 16,
    "filtered": 10,
    "Extra": "Using where; Using filesort"
  },
  {
    "id": 2,
    "select_type": "SUBQUERY",
    "table": "trello_lists",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "PRIMARY",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 2,
    "filtered": 50,
    "Extra": "Using where"
  }
]

First result set (using WHERE IN):

+--+-------+-----------+-----------+-------------------+-------------------+
|id|list_id|customer_id|is_archived|created_at         |updated_at         |
+--+-------+-----------+-----------+-------------------+-------------------+
|1 |1      |1          |1          |2021-02-23 07:30:09|2021-02-23 07:30:09|
|2 |1      |1          |0          |2021-02-23 07:30:09|2021-02-23 07:30:09|
|9 |2      |1          |1          |2021-02-23 07:30:09|2021-02-23 07:30:09|
+--+-------+-----------+-----------+-------------------+-------------------+

Second explain (using UNION):

[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "trello_cards",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 16,
    "filtered": 6.25,
    "Extra": "Using where"
  },
  {
    "id": 2,
    "select_type": "UNION",
    "table": "trello_cards",
    "partitions": null,
    "type": "ref",
    "possible_keys": "trello_cards_list_id_foreign",
    "key": "trello_cards_list_id_foreign",
    "key_len": "8",
    "ref": "const",
    "rows": 8,
    "filtered": 6.25,
    "Extra": "Using where"
  },
  {
    "id": null,
    "select_type": "UNION RESULT",
    "table": "<union1,2>",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": null,
    "filtered": null,
    "Extra": "Using temporary; Using filesort"
  }
]

Second resultset (using UNION):

+--+-------+-----------+-----------+-------------------+-------------------+
|id|list_id|customer_id|is_archived|created_at         |updated_at         |
+--+-------+-----------+-----------+-------------------+-------------------+
|1 |1      |1          |1          |2021-02-23 07:30:09|2021-02-23 07:30:09|
|9 |2      |1          |1          |2021-02-23 07:30:09|2021-02-23 07:30:09|
|2 |1      |1          |0          |2021-02-23 07:30:09|2021-02-23 07:30:09|
+--+-------+-----------+-----------+-------------------+-------------------+
Activity icon

Awarded Best Reply on Join Two Queries At The Model Level

That is what I commented last:

You might need to remove the ->limit(1) depending on the database you are using.

Just remove the ->limit(1)

public function done()
{
    return $this->hasMany(TrelloCard::class, 'customer_id')
        ->where(function ($query) {
            $query->orWhere('is_archived', 1);
            $query->orWhereIn('list_id', TrelloList::select('id')->where('name', 'DONE'));
        });
}
Activity icon

Replied to Join Two Queries At The Model Level

That is what I commented last:

You might need to remove the ->limit(1) depending on the database you are using.

Just remove the ->limit(1)

public function done()
{
    return $this->hasMany(TrelloCard::class, 'customer_id')
        ->where(function ($query) {
            $query->orWhere('is_archived', 1);
            $query->orWhereIn('list_id', TrelloList::select('id')->where('name', 'DONE'));
        });
}
Activity icon

Replied to Why Composer.json Requires PHP 7.4 But Server Is Running PHP 7.3 Doesn't Break In Deployment?

Adding on what @jlrdw said, my bet is that the PHP version running on the CLI might be different from the one configured on FPM.

If you can ssh into that server run:

$ php -v

And check the CLI version PHP is running.

As composer install is run as a CLI command, that might be where the difference lies.

Activity icon

Replied to Join Two Queries At The Model Level

As you are "unioning " two queries from the same table, I would suggest to convert to using OR where clauses.

Try using this:

public function done()
{
    return $this->hasMany(TrelloCard::class, 'customer_id')
        ->where(function ($query) {
            $query->orWhere('is_archived', 1);
            $query->orWhereIn('list_id', TrelloList::select('id')->where('name', 'DONE')->limit(1));
        });
}

You might need to remove the ->limit(1) depending on the database you are using.

Feb
22
1 week ago
Activity icon

Replied to Error Responses

You could customize your app's Exception Handler to normalize the error responses.

The docs outlines how to do it:

https://laravel.com/docs/8.x/errors#rendering-exceptions

You would add something like this:

$this->renderable(function (\Throwable $e, Request $request) {
    if (! $request->expectsJson()) {
        return null; // let Laravel handle it
    }

    if ($e instanceof ValidationException) {
        return null; // let Laravel handle it
    }

    if ($e instanceof AuthenticationException) {
        return null; // let Laravel handle it
    }

    $message = $this->isHttpException($e) ? $e->getMessage() : 'Server Error';
    $statusCode = $this->isHttpException($e) ? $e->getStatusCode() : 500;

    return response()->json([
        'message' => $message,
        'errors' => [],
    ], $statusCode);
});

Hope this helps.

Activity icon

Replied to How Do You Speed Up Envoyer Deployments?

composer install should not be a problem, even when using composer version 1, as it reads from the composer.lock file (which I assume you commit it as it is the default)

For frontend dependencies, I moved on compiling my assets locally and pushing them already processed so I don't even need to have node/npm/yarn installed on the server (unless I am using some integration which needs it).

I keep public/js, public/css, and public/mix-manifest.json (and any other generated frontend file/folder) on my .gitignore file, so they don't mess up every commit if I am using yarn watch (or npm run watch).

But then I have a "build-push.sh" script which runs yarn prod and force add the generated files to a separated commit when I am building to production.

Here is a simplified version of this script:

#!/usr/bin/env bash
# Ensure script is running on the project's folder
cd /home/rodrigo/code/project || exit

# Ensure no changes in generated assets are commited by mistake
git reset HEAD public
git checkout -- public

# Commit changes with message from command line or a default one  
git add .
git commit -m "${1:-savepoint}"

# Ensure all generated assets are removed, recompiled and force added to a separated commit 
rm -r public/css public/js public/fonts public/images public/favicon public/favicon.ico public/mix-manifest.json
yarn run prod
git add --force public/css public/js public/fonts public/images public/favicon public/favicon.ico public/mix-manifest.json
git commit -m 'frontend compilation'

# Push commits to git
git push origin master

# Trigger Forge Deploy
curl  https://forge.laravel.com/servers/...

notify-send 'deployed'

Hope this helps.

Activity icon

Replied to SQL - WHERE IF Condition In QUERY BUILDER Request

You’re welcome! Have a nice day =)

Activity icon

Awarded Best Reply on SQL - WHERE IF Condition In QUERY BUILDER Request

My try:


Route::get('/test', function () {
    $startDate = '2021-02-22';
    $endDate = '2021-02-22';
    $days = [1];
    $locations = [5];

    $constraints = function ($query) use ($locations, $days, $startDate, $endDate) {
        $query->whereIn('location_id', $locations);
        $query->whereIn('day', $days);

        $model = $query->getModel();

        $query->where(function ($query) use ($model, $startDate, $endDate) {
            $query->orWhere(function ($query) use ($startDate, $endDate) {
                $query->where('type', 'custom');
                $query->whereBetween('date', [$startDate, $endDate]);
            });

            $query->orWhere(function ($query) use ($model, $startDate, $endDate) {
                $query->where('type', 'default');
                $query->whereNotExists(function ($query) use ($model, $startDate, $endDate) {
                    $query->selectRaw(1);
                    $query->from($model->getTable(), 'inner');
                    $query->whereColumn('user_id', $model->qualifyColumn('user_id'));
                    $query->whereColumn('day', $model->qualifyColumn('day'));
                    $query->whereColumn('location_id', '<>', $model->qualifyColumn('location_id'));
                    $query->where('type', 'custom');
                    $query->whereBetween('date', [$startDate, $endDate]);
                });
            });
        });
    };


    return \App\Models\User::query()
        ->with(['workingLocations' => $constraints])
        ->whereHas('workingLocations', $constraints)
        ->get();
});

Basically it checks if there is a "custom" record within the dates OR there is not a custom record on a different location for the same date.

Hope this helps.

Feb
21
1 week ago
Activity icon

Replied to How To Customize Laravel Spatie Backup Notification Email ?

On this page the config file is listed:

https://spatie.be/docs/laravel-backup/v7/installation-and-setup

Close to the notifications key there is this comment:

    /*
     * You can get notified when specific events occur. Out of the box you can use 'mail' and 'slack'.
     * For Slack you need to install laravel/slack-notification-channel.
     *
     * You can also use your own notification classes, just make sure the class is named after one of
     * the `Spatie\Backup\Events` classes.
     */

So:

  • Install the package
  • Publish its config (installation show how)
  • Create a notification that extends from \Spatie\Backup\Notifications\Notifications\BackupWasSuccessfulNotification and name it the same on your local app's namespace
  • Update the config file replacing:
\Spatie\Backup\Notifications\Notifications\BackupWasSuccessfulNotification::class => ['mail'],

With:

\App\Notifications\BackupWasSuccessfulNotification::class => ['mail'],

I am assuming you will create the notification within the default path for notifications.

On your custom notification you can format it the way you want/need.

Activity icon

Replied to Loop Row In Bootstrap Every 3 Columns

col-4 will already wrap around every 3 columns. As Bootstrap 4 uses flex instead of floats for rows, there will be always 3 divs per row.

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">

<div class="row">
    @foreach(range(1, 12) as $record)
        <div class="col-4">{{ $record }}</div>
    @endforeach
</div>

If you really want to manually add a .row div every 3 elements, you can use blade's $loop variable:

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">

@foreach(range(1, 12) as $record)
    @if($loop->index % 3 === 0)
        <div class="row">
    @endif

        <div class="col-4">{{ $record }}</div>

    @if($loop->index % 3 === 0)
        </div>
    @endif
@endforeach

https://laravel.com/docs/8.x/blade#the-loop-variable

Activity icon

Replied to SQL - WHERE IF Condition In QUERY BUILDER Request

Note:

I used a closure for the relation constraints so I could apply it both to the eager-loading and the whereHaving for debugging purposes.

But if you don't need to eager load the related model, you can inline it into the whereHaving.

Activity icon

Replied to SQL - WHERE IF Condition In QUERY BUILDER Request

My try:


Route::get('/test', function () {
    $startDate = '2021-02-22';
    $endDate = '2021-02-22';
    $days = [1];
    $locations = [5];

    $constraints = function ($query) use ($locations, $days, $startDate, $endDate) {
        $query->whereIn('location_id', $locations);
        $query->whereIn('day', $days);

        $model = $query->getModel();

        $query->where(function ($query) use ($model, $startDate, $endDate) {
            $query->orWhere(function ($query) use ($startDate, $endDate) {
                $query->where('type', 'custom');
                $query->whereBetween('date', [$startDate, $endDate]);
            });

            $query->orWhere(function ($query) use ($model, $startDate, $endDate) {
                $query->where('type', 'default');
                $query->whereNotExists(function ($query) use ($model, $startDate, $endDate) {
                    $query->selectRaw(1);
                    $query->from($model->getTable(), 'inner');
                    $query->whereColumn('user_id', $model->qualifyColumn('user_id'));
                    $query->whereColumn('day', $model->qualifyColumn('day'));
                    $query->whereColumn('location_id', '<>', $model->qualifyColumn('location_id'));
                    $query->where('type', 'custom');
                    $query->whereBetween('date', [$startDate, $endDate]);
                });
            });
        });
    };


    return \App\Models\User::query()
        ->with(['workingLocations' => $constraints])
        ->whereHas('workingLocations', $constraints)
        ->get();
});

Basically it checks if there is a "custom" record within the dates OR there is not a custom record on a different location for the same date.

Hope this helps.

Feb
20
2 weeks ago
Activity icon

Replied to MySQL Performance With URL Slug As Key?

Good to see you around @tray2 , I still have a lot work to do for a while, but I will catch up some day =)

Activity icon

Replied to MySQL Performance With URL Slug As Key?

Be sure to have an index on the slug column, preferably an unique index as the slug should be unique across posts.

As you are searching slugs by an exact match, and not by the LIKE operator, performance shouldn't be much worse than using the id, if you have an index in place for the slug column.

It will be slightly worse, as an index for a integer sequence (such as an auto-increment id) is, usually, stored and organized very efficiently. "Usually" refer if you don't have a lot of "holes" (missing numbers) on your sequence.

Activity icon

Replied to Laravel - Should Pipelines Replace Queries

I saw some people using pipelines to apply complex filters in isolation, and even sharing filters.

I always thought it is a lot of work, but maybe your filter requirements are complex enough to justify it.

Anyway, here is some related material on using Pipelines with query builder I saved from when I was learning how to use Pipelines properly:

Hope this helps.

Activity icon

Replied to How To Refactor Large Repositories?

Just for the record, the link to the RecursiveIteratorIterator class:

https://www.php.net/manual/en/class.recursiveiteratoriterator.php

One project from which I learned how to work with it was this:

https://github.com/sebastianbergmann/php-code-coverage/blob/3dd5d2deb8bbfd0e13c221330d9170689897393e/src/Node/Directory.php#L89-L95

The class linked above has a lot of specific code to its use-case that I ended up not using, but it was a good starting point at the time.

Activity icon

Replied to How To Refactor Large Repositories?

One thing I noted by re-reading the thread is the we derailed from your original question.

In another project I worked with I had a tree-like data structure for managing a large tree.

I had a TreeBuilder that built a Tree object which was basically to hold Node instances. I used a TreeNode interface and had three implementations: Node (which was basically where the data lied), RootNode (a marker for the root node to facilitate traversal) and NullNode (to model dead ends and avoid null checks when traversing the tree).

The TreeNode interface had a changeParent and changeChildren methods that allowed me to manipulate the tree nodes in memory directly in the node instances.

The Tree object was just a proxy to the RootNode and let apply some high-level operations (filter, map, traversal, etc.) from the RootNode.

I know it seems to be breaking the CQRS pattern I just explained on my last post, but I as it was basically a data-structure code (that held data in-memory), no storage related code was present in the nodes, nor in any tree-manipulation methods.

Every node implemented the IteratorAggregate interface (from PHP itself) and I used a RecursiveIteratorIterator (I know it is a bad name, but it is a actually PHP built-in class) to traverse the tree in a linear fashion.

So after manipulating the tree in memory, I then had action classes that traversed/iterated the tree and actually persisted the changes to the database.

So basically the TreeBuilder read data from database and built the data-structure, and action classes were responsible to persist the tree back to the database. The tree itself was a data-structure unaware of any storage specific code.

I unfortunately can't share the code for this, as it was done under a contract and I would have to ask the client for permission to share it.

But it was a very nice implementation, I used generators for building the tree from the database, and learned a lot about iterators when writing the traversal code.

Hope this helps, somehow, with your original problem.

Activity icon

Replied to How To Refactor Large Repositories?

I know I said my last comment was the final one about the code, but just to clarify one thing:

This project had a requirement to different roles for different modules, and users could have one role for each module.

So there is also a CreateAccountUser, and others. That is why there is a lower level CreateUser, which is shared between the actions specific to a module.