Converting a SQL query to Eloquent

hi guys, I have an SQL query that works just fine but it has a problem, here is the query:

$products = DB::connection('mysql2')
			->select(DB::raw("
				select p.ID as id,
				p.post_title as title,
				p.post_name as url,
				max(case when m.meta_key = 'preview' then m.meta_value end) as preview,
				max(case when m.meta_key = '_thumbnail_id' then m.meta_value end) as image
				from wpneeds_posts p
				LEFT JOIN wpneeds_postmeta m on p.ID = m.post_id
				WHERE p.post_type = 'product' AND p.post_status = 'publish' AND (LENGTH(REPLACE(post_content,'\r\n',' ')) - LENGTH(REPLACE(REPLACE(post_content,'\r\n',''), ' ', '')) + 1) < 400
				group by p.ID
			"));

the problem is I cannot use the "paginate" method on this so I decided to convert it to eloquent for pagination but I had lots of problems doing that. can anyone help me with this?

fylzero

5 years ago

Best Answer

Level 67

@mozex Should be pretty close...

$query = DB::connection('mysql2')
  ->table('wpneeds_posts as p')
  ->select([
    'p.ID as id',
    'p.post_title as title',
    'p.post_name as url',
    DB::raw("max(case when m.meta_key = 'preview' then m.meta_value end) as preview"),
    DB::raw("max(case when m.meta_key = '_thumbnail_id' then m.meta_value end) as image")
  ])
  ->leftJoin('wpneeds_postmeta as m', 'p.ID', 'm.post_id')
  ->where('p.post_type', 'product')
  ->where('p.post_status', 'publish')
  ->whereRaw("(LENGTH(REPLACE(post_content,'\r\n',' ')) - LENGTH(REPLACE(REPLACE(post_content,'\r\n',''), ' ', '')) + 1) < 400")
  ->groupBy('p.ID')
  ->get();  // Obviously can use pagination methods instead

You can use toSql() instead of get() to see the query in case I made any typos or anything needs to be adjusted. Using Tinkerwell combined with toSql() is great for building these out.

1 like

mozex

5 years ago

Level 16

Thank you so much for your help and the awesome tip.

1 like

Please or to participate in this conversation.