Convert SQL query to Laravel Query Builder

Question

I am new to Laravel I have a complex query which is a combination of sub-queries and joins. But not sure how can I convert raw query to Laravel query format. Here is how my query looks like.

I am facing problem mostly dealing with AS as this is not a function available in query builder I am trying to sort out the CASE as well, but u1 and u2 looks undefined to it.

select  `ep`.`id`, `ep`.`title`, `ep`.`slug`, `ep`.`status`, `ep`.`active`,`ep`.`visitor_id`,`ep`.`type`, `friends`.`customername`, `ep`.`created_at`, epblock.reference_id  from enr_posts as ep

JOIN (SELECT  
        (CASE WHEN u2.id = 3
              THEN u1.name
              ELSE u2.name
             END ) as customername,
        (CASE WHEN u2.id = 3
              THEN u1.id
              ELSE u2.id
             END ) as customer_id,
        fr.status
        FROM enr_friend_requests fr
        JOIN enr_customers u1 ON fr.sent_by=u1.id
        JOIN enr_customers u2 ON fr.received_by=u2.id

        where (fr.sent_by=3 or fr.received_by=3) AND fr.status="accepted") as friends 
ON ep.visitor_id=friends.customer_id
LEFT JOIN enr_post_customer_block as epblock
ON `ep`.`id` = epblock.reference_id AND epblock.customer_id=3

WHERE
    `ep`.`active`=1 AND
    `ep`.`status`=1 AND
    epblock.reference_id IS NULL
ORDER BY 
    `ep`.`id` DESC

Is there any generator tool online as well? Because few tools I have looked into but they are not working for me.

Thank you!

Ismoil Shifoev · Accepted Answer · 2018-12-10 08:02:25Z

1

You can convert raw and legacy SQL queries into a Laravel database query builder version with Orator, an online tool by Maurice Calhoun. This generator could also be an excellent tool for someone learning Laravel, as it can help them translate SQL queries into query builder objects, as learning a new ORM can sometimes be a challenge for new developers.

More information here

https://laravel-news.com/convert-sql-laravel-builder-orator

answered Dec 10, 2018 at 8:02

Ismoil Shifoev

6,0893 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3201500 Over a year ago

Is there any link to try it online?

Ismoil Shifoev Over a year ago

orator.readthedocs.io/en/latest this is documentation)) on article I send you. You can see

user3201500 Over a year ago

I just tried the link you provided it looks the website is expired.

Ismoil Shifoev Over a year ago

Let us continue this discussion in chat.

D-Shih · Accepted Answer · 2022-05-08 12:56:12Z

I had found a website that might be worked at complex SQL to Query Builder SQL TO LARAVEL BUILDER, that website was created by the sql-to-laravel-builder repository.

As your complex query, it might convert as.

DB::table('enr_posts as ep')
->select('`ep`.`id`','`ep`.`title`','`ep`.`slug`','`ep`.`status`','`ep`.`active`','`ep`.`visitor_id`','`ep`.`type`','`friends`.`customername`','`ep`.`created_at`','epblock.reference_id')
->join('(SELECT  
        (CASE WHEN u2.id = 3
              THEN u1.name
              ELSE u2.name
             END ) as customername,
        (CASE WHEN u2.id = 3
              THEN u1.id
              ELSE u2.id
             END ) as customer_id,
        fr.status
        FROM enr_friend_requests fr
        JOIN enr_customers u1 ON fr.sent_by=u1.id
        JOIN enr_customers u2 ON fr.received_by=u2.id

        where (fr.sent_by=3 or fr.received_by=3) AND fr.status=\"accepted\") as friends','ep.visitor_id','=','friends.customer_id')
->leftJoin('enr_post_customer_block as epblock',function($join) {$join->on('`ep`.`id`','=','epblock.reference_id')
->where('epblock.customer_id','=',3); })
->where('`ep`.`active`','=',1)
->where('`ep`.`status`','=',1)
->whereNull('epblock.reference_id')
->orderBy('`ep`.`id`','DESC')
->get();

NOTE

But sometimes created code of Query Builder might not be the best performance, but we can modify based on that to help us improve our developing time.

so I would suggest reading eloquent as well

Collectives™ on Stack Overflow

Convert SQL query to Laravel Query Builder

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related