Propel - Filter with many-to-many relationship

2
votes

I try to use Propel and don't succed to filter with many-to-many relationship.

Here, find schema.xml:

<?xml version="1.0" encoding="UTF-8"?>
<database defaultIdMethod="native" name="flash">
  <table name="demand">
    <column name="id" type="INTEGER" primaryKey="true" autoIncrement="true" required="true" />
    <column name="label" type="VARCHAR" size="90" required="true" />
    <column name="description" type="LONGVARCHAR" required="true" />
  </table>
  <table name="customer">
    <column name="id" type="SMALLINT" primaryKey="true" autoIncrement="true" required="true" />
    <column name="name" type="VARCHAR" size="32" required="true" />
    <column name="service_id" type="SMALLINT" required="true" />
    <foreign-key name="fk_customer_service" foreignTable="service">
      <reference local="service_id" foreign="id"/>
    </foreign-key>
  </table>
  <table name="service">
    <column name="id" type="SMALLINT" primaryKey="true" autoIncrement="true" required="true" />
    <column name="label" type="VARCHAR" size="4" />
  </table>

  <table name="demand_customer" isCrossRef="true">
    <column name="demand_id" type="INTEGER" primaryKey="true" required="true" />
    <column name="customer_id" type="SMALLINT" primaryKey="true" required="true" />
    <foreign-key name="fk_demand_customer_demand" foreignTable="demand" onDelete="CASCADE">
      <reference local="demand_id" foreign="id"/>
    </foreign-key>
    <foreign-key name="fk_demand_customer_customer" foreignTable="customer" onDelete="CASCADE">
      <reference local="customer_id" foreign="id"/>
    </foreign-key>
  </table>
</database>

I would like to retrieve all demands with service of customers attached is equal to 3 (for example). With data below, I would just have demand id number 2.

Could you help me ?

Data examples :

    demand
-------------------------------
id  |   label   |   description
-------------------------------
1       test1       desc1
2       test2       desc2


customer
------------------------------
id  |   name    |   service_id
------------------------------
1       name1       1
2       name2       3
3       name2       2


service
-------------
id  |   label
-------------
1       service1
2       service2
3       service3


demand_customer
---------------------------
demand_id   |   customer_id
---------------------------
1               1
2               1
2               2
1
many-to-manyrelationshippropel
Have you made any attempt in doing this? If so, show the snippet and state where you're getting stuck – Qiniso
I really don't succeed. I just translate my needs in SQL : SELECT * FROM demand d WHERE d.id IN ( SELECT demand_id FROM demand_coworker WHERE coworker_id IN ( SELECT c.id FROM coworker c, service s WHERE c.service_id = s.id AND s.id = 2 ) ) – z0om

1 Answers

0
votes

I found how to do this :

$demands = DemandQuery::create()
->useDemandCustomerQuery()
    ->useCustomerQuery()
        ->filterByServiceId(getSessionValue('service'))
    ->endUse()
->endUse()
->find();