0

I try to figure out how I get all Elements A where the referenced Element B has a value in a special field.

Lets say i have the follow models:

Person
id  name    active  companyref
1   Pers1   1       1
1   Pers2   1       2

Company
id  name    filter
1   Comp1   1
1   Comp2   5

When I want to get all the active persons I do the following in my repository:

function findAllActive() {
 $query = $this->createQuery();
 return $query->matching(
            $query->equals('aktiv', 1)
        )->execute();
}

Now my question is how can I get all persons where the refCompany got filter=5?

Edit: after the answer of freshp dont work I post my precise case:

this will work (I write the sql by my own):

function findAllActiveForPaketstufe($paketstufe) { 
 $query = $this->createQuery();
 $sql="SELECT p . * 
FROM  `tx_myext_domain_model_person` AS p
INNER JOIN tx_myext_domain_model_firma AS f ON f.uid = p.firma_ref
WHERE paketstufe =3";

 return $query->matching(
        $query->equals('aktiv', 1)
        )->execute();
}

and this will not work (I write the sql only half):

function findAllActiveForPaketstufe($paketstufe) { 
 $query = $this->createQuery();
 $sql="SELECT p . * 
FROM  `tx_myext_domain_model_person` AS p
INNER JOIN tx_myext_domain_model_firma AS f ON f.uid = p.firma_ref";

 return $query->matching(
            $query->logicalAnd(
                $query->equals('aktiv', 1),
                $query->equals('person.firmaref.paketstufe', intval($paketstufe))
            )
        )->execute();
}

I get the Error: Fatal error: Call to a member function getParentKeyFieldName()

Instead of "person.firmaref.paketstufe" I also tried:

  • firmaref.paketstufe
  • p.firmaref.paketstufe
  • firma_ref.paketstufe
  • and so on

And what I realy want is something like this (I dont have to write the sql at all):

 $query = $this->createQuery();
 return $query->matching(
      $query->locicalAnd(
            $query->equals('companyref.filter', 5),
            $query->equals('active', 1)
      )
 )->execute();

How can I get this to work?

nbar
  • 6,028
  • 2
  • 24
  • 65

1 Answers1

1

You can do it like:

function findAllActive() {
 $query = $this->createQuery();
 return $query->matching(
      $query->locicalAnd(
            $query->equals('companyref.filter', 5),
            $query->equals('active', 1)
      )
 )->execute();
}
freshp
  • 525
  • 5
  • 20
  • I try it like that, but no matter what I do i get: Fatal error: Call to a member function getParentKeyFieldName() on a non-object in /var/www/hosts/dev.itrocks.next-linux2.ch/typo3/sysext/extbase/Classes/Persistence/Storage/Typo3DbBackend.php on line 660 – nbar Sep 03 '13 at 07:12
  • I added a more precise example. Maybe you can figure out now where my problem is. – nbar Sep 03 '13 at 07:28
  • which typo3 version and extbase version do you use? – freshp Sep 03 '13 at 07:55
  • Thx for the link, it was the missing cammelCase. $query->equals('firmaRef.paketstufe', intval($paketstufe) – nbar Sep 03 '13 at 08:29