Select from table where has at least one child with value

Need Help
1

Hii There,

I’m trying to make a little JSON API which I can use to get data about my projects.
For this, I’m first starting off with making a list of all projects found in the tables with only one condition: The project has to have at least one release (found in the projects_releases table with an association) which has it’s published value set to 1.
If the project has multiple releases which have this, then ignore this.

It doesn’t need to include the data of any release, just be in my object if it has one (or more) releases and ignored when it has none.

I currently have this code, but it results in duplicate entries in the final object :\

$projects = $projectsTable
        ->find()
        ->leftJoinWith('ProjectsReleases', function ($q) {
          return $q->where(['published' => 1]);
        });

I hope this makes sense?
Cheers

2

This code seems to have worked fine:

$projects = $projectsTable
        ->find()
        ->distinct(['Projects.id'])
        ->leftJoinWith('ProjectsReleases', function ($q) {
          return $q->where(['published' => 1]);
        });

nvm it still shows the project when it has no qualifying release…

3

In the end, I found this as working solution to my problem:

$projects = $projectsTable
        ->find()
        ->select(['Projects.id'])
        ->select($projectsTable)
        ->matching('ProjectsReleases', function ($q) {
          return $q->where(['published' => 1]);
        })
        ->distinct(['Projects.id']);
4

If you have the relationship configured in the table, I believe you can do this with a
->contains…

for any other relevant fields you need, including from any related tables.fields.

5

I could use contains yes but it’d contain all the related entities instead of just the ones with the flag I want which, I’d then have to filter and process (which wastes precious resources).