Laravel - 获取记录集合,如果不存在于另一个表中

I need to get all records from table IF these records not exists in another table, e.g.
Table 'Cars'

| ID | Name_of_car  
+----+----------------  
| 1 .| Toyota  
+----+----------------  
| 2 .| Ford

Table 'Crashed_cars'

| car_id | crash_date  
+--------+----------------  
| 1 .....| 22-02-2016  

Now I want to get all not crashed cars - how can I do this?
At the moment I use a loop, but due to the number of entries (about 3000) I would have to use the direct collection. Here is my query:

Cars::select( 'id', 'firstname', 'lastname', 'car', 'color' )->get();

2个回答

The best way is probably to get the ids of all crashed cars, and then searching for all other cars. In other words, something like this:

$crashedCarIds = CrashedCar::pluck('car_id')->all();
$cars = Car::whereNotIn('id', $crashedCarIds)->select(...)->get();



您也可以使用一个查询获得相同的结果,这将提高您的操作速度。</ p>
\ n

可以使用 FULL OUTER JOIN </ strong>来实现这一点,例如以下文章中的示例:</ p>

  select * from dbo.Students  S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID = A.Advisor_ID,其中A.Advisor_ID为空
</ code> </ pre>

文章: http://www.datamartist。 com / sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server no。 6。</ p>

祝你好运!</ p>
</ div>

展开原文

原文

You could also get the same result with one single query, which will increase the speed of your action.

One could use a FULL OUTER JOIN to achieve this, like this example from the following article:

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null

Article: http://www.datamartist.com/sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server no. 6.

Good luck!

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐