Eloquent tricks

By @lars · 2021-08-30 13:48

Eloquent tricks

Here are some eloquent tricks worth trying!

$uniquesAndBounces = $this->site->visitors()
    ->toBase()
    ->selectRaw(<<<SQL
        count(*) uniques,
        count(case when is_bounce then 1 end) bounces
    SQL)
    ->whereBetween('created_at', [$this->fromDate, $this->toDate])
    ->first();

toBase()

You don't always need to have hydrated models, the draw back of hydrating a lot of models is that they consume lots of RAM. With the toBase() method you get a collection of objects.

Heredocs

Make your SQL statements more readable with heredocs rather than a single string. In most cases, your IDE will apply SQL syntax highlighting to the query.

$query->selectRaw(<<<SQL
    count(*) uniques,
    count(case when is_bounce then 1 end) bounces
SQL);

vs

$query->selectRaw('count(*) uniques, count(case when is_bounce then 1 end) bounces');

Case

Okay, this is more a MySQL trick but still worth sharing! Rather than doing another query to receive the count, you can use the case statement.

count(case when is_bounce then 1 end) bounces

Works similar to

select count(*) bounces 
from visitors
where is_bounce = 1

Keep in mind that you can use the case statement for much more than counts. But this is a useful trick which you can apply often!

  • By @samuel · 2021-08-31 18:31

    The heredocs are a good practice, combining code from multiple languages using normal strings is almost always less readable than that. I try to use heredocs for anything multi-line

    • By @lars · 2021-08-31 19:02

      I should probably do it more often too, I usually stick with just using them for SQL 😬

      • By @samuel · 2021-08-31 19:29

        In Lean I have one field which has a template in the PHP class — it works well for HTML too. VSCode recognizes the language, so all of the plugins like Tailwind CSS Intellisense work there.

        protected function badgeTemplate(): Closure
{
    return fn (self $field) => <<<HTML
        <span class="
            inline-flex justify-center items-center px-3 py-0.5 text-sm font-almost-bold rounded-full
            bg-{$field->color()}-100 text-{$field->color()}-800 dark:bg-{$field->color()}-900 dark:text-{$field->color()}-50
        ">
            {$field->displayText()}
        </span>
    HTML;
}

  • By @ralphjsmit · 2021-08-31 09:03

    Hey Lars, great tips! I like especially the first one. One question, what info is available in a model that’s not (yet) hydrated? The id? And are those models hydrated the moment you use them?

    And third, in what situations would this be really recommended? I’ve a little difficulty coming up with good use cases for this :)

    • By @lars · 2021-08-31 19:08

      To answer your first question, you'll just receive the columns that you query from the DB. To answer your second question, counting multiple columns, like todos which can have multiple statuses