How to add a function to an eloquent query?

I have a table of categories. This table has 'category_id' . I need to get all categories that start with the number 1.

I guess you need to use the function:

substr('category_id', 0, 1), 1

I tried to do this, but of course it doesn't work =)))

$categories = Category::withTrashed()
            ->where(substr('category_id', 0, 1), 1)
            ->get();
        dd($categories);

vincent15000

2 years ago

Level 63

Do you mean that you need all categories for which the id starts with 1 ? That's very strange ... why such a need ?

tykus

2 years ago

Best Answer

Level 104

You're using the PHP substr method there, note the MySQL version (note the PHP string begins at character 0)

substr('category_id', 0, 1) // 'c'

So, if I understand what you need, this would be the query (note that the SQL string begins at character 1):

$categories = Category::withTrashed()
    ->whereRaw("substr(category_id, 1, 1) = 1")
    ->get();

As @vincent15000 says, this is a very strange query; what is the actual use case / story?

1 like

Snapey

2 years ago

Level 122

You should not be doing anything that is based on some numerical value of record id. You will have very brittle code, magic numbers in your code, and problems down the line.

So thats three people to say dont do this. How many more do we need?

1 like

Please or to participate in this conversation.