Convert this SQL to Laravel

I have written this SQL and now I need to convert it to Laravel Query Builder.

The idea is that I can get the age of a date field. I did it by using the PostgreSQL built in method called age, the thing is that I need to filter users by the age, for example, users with age equal or greater than 24.

There are two ways to accomplish this:

The table:

CREATE TABLE test (
  id INT,
  name VARCHAR(255),
  birthdate DATE
);

INSERT INTO test (id, name, birthdate) VALUES (1, 'jhon', '1975-12-12');
INSERT INTO test (id, name, birthdate) VALUES (2, 'doe', '1994-12-12');

The queries:

CTE approach (Common Table Expressions)

WITH cte as (
  SELECT *, extract(year from age(birthdate)) as age 
  FROM test
)  
SELECT id, name, birthdate 
FROM cte
WHERE age >= 24

Lateral join approach

select * 
from test cross join lateral
     (values (extract(year from age(birthdate)))
     ) v(age) 
where v.age >= 24;

If you want to see it live: https://www.db-fiddle.com/f/qA8znjMzm4NR9wdCo5pssX/2

How can I convert one of the approaches (or both) to use Laravel Query Builder?

Thank you very much.

willvincent

6 years ago

Level 54

Any particular reason you don't want to use the raw sql?

It's project specific of course, but I generally recommend not using commands that are specific to only certain sql server engines.. neither of your examples work in mysql, for example, nor would they in sqlite, etc.

AbehoM

6 years ago

Level 2

@willvincent I will be creating a global scope so I can use in multiple models, I think that using Laravel Query Builder makes it easier to understand what is going on. I'm using Postgres in this project and I won't be changing, in order to do the same in other databases is horrible.

AbehoM

6 years ago

Best Answer

Level 2

I managed to do like this:

return Users::crossJoin(
    DB::raw('lateral (values(extract(year from age(birthday)))) v(age)')
)->where('age', 25)->get();

But I'm wondering if I'm using the crossJoin right or if there are ways to make this cross join lateral.

Please or to participate in this conversation.