Propel ORM: Add a column from another table which is not connected with a direct foreign key

0
votes

I want to add a SQL column from another table to my Propel query but I can´t get this with the normal "->withColumn" Statement. The second table is not connected with the first by a foreign key, but it has for every entry in the first exactly one entry in the second. The connection between the two tables are two ids from two more tables.
For a better understanding, here are my tables:

entry:

  • id
  • name
  • expert_id
  • contingent_id

favorit:

  • id
  • position
  • expert_id
  • contingent_id

expert and contingent:

  • id
  • ...

I want all entries from the entry-table with an additional column position. There exists for every entry exact one favorit. With SQL this query is no problem:

SELECT *, (SELECT position FROM favorit AS f WHERE e.expert_id = f.expert_id AND e.contingent_id = f.contingent_id) AS position FROM entry AS e

But I don´t know how to create the query with Propel. I tried it with this:

EntryQuery::create() ->filterByExpertId(1) ->withColumn("select position from favorit AS f where e.expert_id = f.expert_id and e.contingent_id = f.contingent_id", '_favorit_id', 'type: int') ->find();

But Propel gives me an error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select position from favorit AS f where e.expert_id = f.expert_id and e.continge' at line 1' in D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\Adapter\Pdo\PdoStatement.php:57 Stack trace: #0 D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\Adapter\Pdo\PdoStatement.php(57): PDOStatement->execute(NULL) #1 D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\Connection\StatementWrapper.php(194): Propel\Runtime\Adapter\Pdo\PdoStatement->execute(NULL) #2 D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\ActiveQuery\Criteria.php(2633): Propel\Runtime\Connection\StatementWrapper->execute() #3 D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\ActiveQuery in D:\Entwicklung\htdocs\zeiterfassung\vendor\propel\propel\src\Propel\Runtime\ActiveQuery\Criteria.php on line 2639

I have no problem to add a column from another table when there exists in the entry-table a foreign key to the other table like contingent_id or with calculated columns:
EntryQuery::create() ->filterByExpertId(1) ->join('Entry.Contingent') ->withColumn('Contingent.name','_contingentName') ->withColumn("LEFT(TIMEDIFF(TIMEDIFF(timeEnd,timeBegin),pause),5)", '_duration', 'type: time') ->find();

1
phpsqlormpropel

1 Answers

0
votes

I found a solution for my problem:

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

With this I got the correct answer. But I got one more question:

If there exist to an entry a corresponding expert and contingent but no favorit, so this entry will with my actual query not displayed. I want as an extra in these cases this entry too but with a position with some default value like null or 0 (zero).