Filter query by column in joined table

[kevinmichaelchen]

I have a simple data model: dogs and humans.
A dog belongs to a human (i.e., dog has the foreign key).
I want to make a query that filters on a column in the table to which I'm joining.

SELECT h.name, d.name
  FROM dogs d
  JOIN humans h ON h.id = d.owner_id
  WHERE h.name = 'Kevin'

I'm wondering if this is possible using eager loading, but ultimately eager loading is not necessary.

Ideally, I'd like to avoid raw SQL, and I'd like to avoid writing out all columns in my query.

[kevinmichaelchen]

Here's what I've come up with:

dogs, err := models.Dogs(
  // eager load the dog's hooman
  qm.Load(models.DogRels.Human, 
    qm.Where(fmt.Sprintf("%s.%s = ?", models.TableNames.Humans, models.HumanColumns.Name), humanNames[0]),
  ),
  qm.Limit(5),
).All(ctx, exec)

The dogs slice will sometimes have an entity with a nil Human (if for example there are fewer than limit dogs that meet the where criteria).

[kevinmichaelchen]

Using an InnerJoin means we filter out the records that don't match our WHERE criteria.

func queryForDogs() {
	dogs, err := models.Dogs(
		// eager load the dog's hooman
		qm.Load(models.DogRels.Human),
		qm.InnerJoin(
			fmt.Sprintf(
				"%s on %s.%s = %s.%s",
				models.TableNames.Humans,
				models.TableNames.Humans,
				models.HumanColumns.ID,
				models.TableNames.Dogs,
				models.DogColumns.HumanID,
			),
		),
		qm.Where(
			fmt.Sprintf(
				"%s.%s = ?", 
				models.TableNames.Humans,
				models.HumanColumns.Name,
			),
			humanNames[0],
		),
		qm.Limit(5),
	).All(ctx, exec)
}

[firminochangani]

Thanks @kevinmichaelchen!!!