Using previous table for row count gets modified

[slugger7]

Hi everyone,

I have come across a pretty strange situation that I am not really sure how to solve.

I am trying to use a previous select statement as a table to feed to a count statement to determine the amount of rows that a search would have before applying the limit and offset (see relevant lines)

The problem that I am having is that the count statement's SQL actually has the limit and offset set on it which means that the count will always be less than or equal to the limit (not the intended behaviour).

Ok, that makes sense the select statement is used by reference at that point to my knowledge and I should just clone it to make a fresh memory copy of the select statement in order to avoid this.

I looked for something like that from Jet but could not find anything.
Is there another way to do something like this? Or would I maybe need to do a sneaky JSON.Parse(JSON.stringify(selectStatement)) with rawSql?

Please also find below the sql output from the count statement.

Relevant lines {#relevant}

...
	countStatement := selectStatement.AsTable("media_overview").SELECT(postgres.COUNT(postgres.String("id")))

	selectStatement = selectStatement.
		LIMIT(int64(search.Limit)).
		OFFSET(int64(search.Skip))

	return selectStatement, countStatement

Count statement SQL

SELECT COUNT('id'::text)
FROM (
          SELECT media.id AS "media.id",
               media.title AS "media.title",
               media.media_type AS "media.media_type",
               thumbnail.id AS "thumbnail.id"
          FROM public.media
               LEFT JOIN public.media_relation ON ((media.id = media_relation.media_id) AND (media_relation.relation_type = 'thumbnail'))
               LEFT JOIN public.media AS thumbnail ON (thumbnail.id = media_relation.related_to)
          WHERE ((media.media_type = 'primary') AND media.deleted IS FALSE) AND media.exists IS TRUE
          ORDER BY media.added DESC
          LIMIT 50
          OFFSET 0
     ) AS media_overview;

[slugger7]

My current solution for this is to wrap the entire section of code that sets up the selectStatement in a function that creates it twice.

Still wondering if there is a better way to do this though that I do not have to create a function to create the select statement twice

[houtn11]

I think you can achieve the same, in one db call, using a window function:

selectStatement = SELECT(
		Media.AllTables,
		COUNT(STAR).OVER().AS("count"),
	).
	WHERE( some conditions ).
	LIMIT(int64(search.Limit)).
	OFFSET(int64(search.Skip))


var dest struct{
	model.Media

	Count int64
}

err := selectStatement.Query(ctx, db, &dest)

[slugger7]

You legend. I think this is probably the best way to actually do this

Thanks!