Left Join Lateral logic for Query()

[VRmnv]

Hello! I have this sql query and i want to rewrite it into bob.

SELECT 	source.uuid,
	iteration.uuid
FROM source
LEFT JOIN LATERAL (
	SELECT 	*
	FROM iteration i WHERE i.source_uuid = source.uuid
	ORDER BY created_at DESC
	LIMIT 3) iteration ON true

I don't know how to implement this behavior using Query(). My Query() looks like this:

sourcesWithIterations, err := models.Sources.Query(
		models.ThenLoadSourceIterations(
			sm.Limit(*iterationsLimit),
		),
	).All(ctx, s.db)

However, i'm getting only first 3 iterations, not first 3 iterations for each source. Is there a common way to get my desired behavior using Query() or i should build query using builder?

[stephenafamo]

As mentioned in the documentation, ThenLoadXXXXX does a separate query. So you should add your additional mods with this in mind.

To illustrate, your current query will run 2 queries:

SELECT * 
FROM sources;

-- Assuming the foreign key column is source_id
SELECT * 
FROM iterations 
WHERE iterations.source_id IN (....)
LIMIT 3; -- this is the mod you added.

What you want to do needs a slightly more complicated 2nd query:

WITH selected AS (
    SELECT id, rank() OVER (PARTITION BY source_id ORDER BY id) as "rank"
    FROM iterations
)
SELECT *
FROM iterations
INNER JOIN selected ON selected.id = iterations.id AND selected.rank <= 3 -- this 3 or less of each source_id
WHERE iterations.source_id IN (....);

Knowing this, we can now attempt to craft in Bob:

models.Sources.Query(
  models.ThenLoadSourceIterations(
    sm.With("selected").As(
      models.Iterations.Query(
        sm.Select(models.IterationColumns.ID),
        psql.F("rank")(fm.Over().
          PartitionBy(models.IterationColumns.SourceID).
          OrderBy(models.IterationColumns.ID)), // Change the order as you like
      ),
    ),
    sm.InnerJoin("selected").On(
      psql.Quote("selected", "id").EQ(models.SourceColumns.ID),
      psql.Quote("selected", "rank").LTE(psql.Raw(*iterationsLimit)),
    ),
  ),
)

Try this and let me know if it works.

[VRmnv]

Thanks for such a quick answer.

I managed to implement this query, thanks to you!

Changed query a little, because one you provided not works.

sm.With("selected").As(
	models.Iterations.Query(
		sm.Columns(
                        models.IterationColumns.UUID,
			psql.F("rank")(fm.Over().
				PartitionBy(models.IterationColumns.SourceUUID).
				OrderBy(models.IterationColumns.CreatedAt),
			),
		),
	),
),
sm.InnerJoin("selected").On(
	psql.Quote("selected", "uuid").EQ(models.IterationColumns.UUID),
	psql.Quote("selected", "rank").LTE(psql.Arg(*iterationsLimit)),
)

Can i change order of my rank function?
fm.Over() have only OrderBy method, no Order

To implement this:

OVER (PARTITION BY source_id ORDER BY id [DESC | ASC] ) as "rank"

[stephenafamo]

This was not possible.

However, I just pushed commit 91f9bef which now uses mods to modify window definitions, making it possible to add ASC/DESC and NULLS FIRST/LAST.

sm.With("selected").As(
    models.Iterations.Query(
        sm.Columns(
            models.IterationColumns.UUID,
            psql.F("rank")(fm.Over(
                wm.PartitionBy(models.IterationColumns.SourceUUID),
                wm.OrderBy(models.IterationColumns.CreatedAt).Desc(), // now possible
            ),
            ),
        ),
    ),
),
sm.InnerJoin("selected").On(
    psql.Quote("selected", "uuid").EQ(models.IterationColumns.UUID),
    psql.Quote("selected", "rank").LTE(psql.Arg(*iterationsLimit)),
),

[VRmnv]

cool! Thanks!