将Mysql查询转换为Laravel雄辩 [英] Convert Mysql Query to Laravel Eloquent

问题描述

我在应用程序中使用Laravel 5.1.

I am using Laravel 5.1 in my application.

我的MySQL查询是

SELECT * FROM (SELECT uu.*,t.left_id AS meb_id,(CAST(t.pair AS UNSIGNED) - IFNULL(p.pair,0)) AS pair FROM (SELECT left_id,
(CASE 
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') >= (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2) 
THEN (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') < (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2) 
THEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') /2)
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') >= (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2)
THEN (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') < (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2)
THEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') / 2) END
) AS pair
FROM user_count AS u WHERE  left_id <> 0 GROUP BY left_id) AS t
LEFT JOIN users AS uu ON uu.`id` = t.left_id
LEFT JOIN `total_payment` p ON t.left_id = p.`meb_id`
WHERE t.pair <> 0) AS f WHERE f.pair > 0  AND f.active = 1

如何在Laravel Eloquent中将其转换?我不知道如何在laravel中雄辩地使用when case.

How Can I convert it in Laravel Eloquent? I don't know how to use when case in laravel eloquent.

我的数据库架构是这样的

My Database schema is like this

user_count表

user表

total_payment表

有人可以帮我吗?

推荐答案

只需用DB :: statement(")包围原始查询，就像这样:

Just surround your raw query with a DB::statement(""), like this :

DB::statement("INSERT INTO `mydatabase`.`mytable` (`id`, `created_at`, `updated_at`, `deleted_at`) VALUES ('7', '2015-09-26 13:38:48', '2015-09-26 13:43:41', NULL)");

它可以很好地完成工作，并且还可以处理种子.

It will do the job fine and works with seeds as well.

这篇关于将Mysql查询转换为Laravel雄辩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！