Propel：即使第二个表没有相应的条目，也可以从2个db表中获取查询结果

My Problem: Getting query results from 2 db tables with PROPEL2 even when second table has no corresponding entries. If the second has corresponding entries than it is no problem.

我的问题：即使第二个表没有相应的条目，也可以从带有PROPEL2的2个db表中获取查询结果。如果第二个有相应的条目比没有问题。

I have 3 tables: Entry, Contingent and Favorit.
The schema is as follow:

我有3个表：Entry，Contingent和Favorit。架构如下：

Entry.id [PK]
Entry.contingent_id [FK]
Entry.expert_id

Entry.id [PK] Entry.contingent_id [FK] Entry.expert_id

Contingent.id [PK]
Contingent.name

Contingent.id [PK] Contingent.name

Favorit.id [PK]
Favorit.contingent_id [FK]
Favorit.expert_id
Favorit.pos

Favorit.id [PK] Favorit.contingent_id [FK] Favorit.expert_id Favorit.pos

I want to get for a specified expert_id ($id) all entries from Entry with contingent-name and if exists the favorit.pos for this expert and contingent. I get the wanted with:

我想获取指定的expert_id（$ id）来自Entry的具有偶然名称的所有条目，并且如果存在此专家和特遣队的favorit.pos。我得到了通缉：

$result = EntryQuery::create()
->filterByExpertId($id)
->join('Entry.Contingent')
->withColumn('Contingent.name','_contingentName')
->join('Contingent.Favorit')
->where('Favorit.expert_id = ?', $id)
->find();

This works only if there exists such a favorit.pos . In some cases this element doesn’t exists (what is wanted from the system). In these cases I want to get the result too just with favorit.pos as empty, null or 0. But Propel doesn’t return me these records.

这只有在存在这样的favit.pos时才有效。在某些情况下，此元素不存在（系统需要什么）。在这些情况下，我想得到的结果也只是使用favorit.pos为空，null或0.但Propel不会返回这些记录。

With MySQL I have no problem to get the desired result:

使用MySQL我没有问题，以获得所需的结果：

SELECT entry.* ,
    (SELECT favorit.position
     FROM contingent, favorit
     WHERE
        favorit.expert_id = entry.expert_id
        AND entry.contingent_id = contingent.id
        AND contingent.id = favorit.contingent_id
    )
FROM `entry`
JOIN contingent
ON  entry.contingent_id = contingent.id
WHERE
    entry.expert_id=1;

1 个解决方案

#1

Use Join left in code:

在代码中使用Join left：

->join('Contingent.Favorit','selection conditon','left' )

This left work when empty database when condition is false in condition like 'id'=$id

当条件为'id'= $ id时条件为false时，这在空数据库时工作

#1