usage with cte queries

[noelsoong]

Hi I ran into an issue when creating a cte query and using it with pagination i get syntax errors.

I have tested my main query sepeartely and this error only happens using knex-paginate.

error message: - Incorrect syntax near ')'

happy to share the query output with you :D

[felixmosh]

What are cte queries? What is your query? 😅

[noelsoong]

This is with lengthAware the

eg. cteQuery.paginate()

select
    top (10) count(*) as [total]
from
    ( ### cte query starts here
    with [cte_events] as (
    select
        [e].[event_id] as [id],
        [e].[event_uid] as [uid],
        [e].[event_name] as [name],
        [ev].[eventvenue_ticketheadermain] as [header],
        [ev].[eventvenue_ticketfootermain] as [byline],
        [e].[event_description] as [description],
        [ev].[eventvenue_extradescription] as [subdescription],
        [ev].[eventvenue_directorypath] as [slug],
        [e].[event_timestamp] as [timestamp],
        [g].[genre_id] as [category.id],
        [g].[genre_name] as [category.name],
        [v].[venue_uid] as [venue.uid],
        [v].[venue_name] as [venue.name],
        [c].[city_name] as [venue.location],
        [tz].[timezone_name] as [venue.timezone],
        lower(p.payee_uid) as [orgnisation.uid],
        [p].[payee_name] as [organisation.name],
        JSON_QUERY
                    (
                      ISNULL
                      (
                        (
        SELECT
            *
        FROM
            dbo.fn_Model_EventImages(e.event_id)
        ORDER BY
            [format.name]
                          FOR JSON PATH
                        ),
        '[]'
                      )
                    ) as [images],
        CASE
            WHEN e.event_active = 0
            AND e.event_exists = 1 THEN 'draft'
            WHEN e.event_active = 1
            AND e.event_exists = 1
            AND ev.eventvenue_closesaledate > getdate() THEN 'published'
            WHEN e.event_active = 1
            AND e.event_exists = 1
            AND ev.eventvenue_closesaledate <= getdate() THEN 'past'
            ELSE 'unknown'
        END as [status],
        [e].[event_active] as [active]
    from
        [tbl_events] as [e]
    inner join [tbl_payees] as [p] on
        [e].[payee_id_producer] = [p].[payee_id]
    inner join [tbl_eventvenues] as [ev] on
        [e].[event_id] = [ev].[event_id]
    inner join [tbl_venues] as [v] on
        [ev].[venue_id] = [v].[venue_id]
    inner join [tbl_timezones] as [tz] on
        [tz].[timezone_id] = [v].[timezone_id]
    inner join [tbl_genres] as [g] on
        [e].[genre_id] = [g].[genre_id]
    left join [tbl_cities] as [c] on
        [v].[city_id] = [c].[city_id]
    where
        ([p].[payee_uid] = '81F50961-C762-410C-91FA-B30F1511D21D'))
    select
        *
    from
        [cte_events]
    where
        ([status] = 'draft')
    ) as [count__query__]

[noelsoong]

This is a query with the WITH clause. I have to use the WITH clause as i have a filter [status] = 'draft' that relies on the [status] column in the child query.

[felixmosh]

I'm not familiar with this dialect, can you share the knex query itself?

[noelsoong]

list()
{
....
const query =  this.db
      .select<EventDetail[]>("*")
      .from<EventDetail[]>("cte_events")
      .with("cte_events", (qb) => {
        qb.from<TableEvent, EventDetail[]>({ e: "tbl_events" })
          .select(
            { id: "e.event_id" },
            { uid: "e.event_uid" },
            { name: "e.event_name" },
            { header: "ev.eventvenue_ticketheadermain" },
            {
              status: this.db.raw(`CASE
      WHEN e.event_active = 0 AND e.event_exists = 1 THEN 'draft'
      WHEN e.event_active = 1 AND e.event_exists = 1 AND ev.eventvenue_closesaledate > getdate() THEN 'published'
      WHEN e.event_active = 1 AND e.event_exists = 1 AND ev.eventvenue_closesaledate <= getdate() THEN 'past'
      ELSE 'unknown'
      END`),
            },
            { active: "e.event_active" }
          )
          .where((qb) => {
            qb.where("p.payee_uid", promoterUid);
            //Filters

            if (filters.eventName) {
              qb.andWhere("event_name", "LIKE", filters.eventName);
            }
          })
          .innerJoin<TableOrganisation>({ p: "tbl_payees" }, "e.payee_id_producer", "p.payee_id")
          .innerJoin({ ev: "tbl_eventvenues" }, "e.event_id", "ev.event_id")
          .innerJoin({ v: "tbl_venues" }, "ev.venue_id", "v.venue_id")
          .innerJoin({ tz: "tbl_timezones" }, "tz.timezone_id", "v.timezone_id")
          .innerJoin({ g: "tbl_genres" }, "e.genre_id", "g.genre_id")
          .leftJoin({ c: "tbl_cities" }, "v.city_id", "c.city_id");
      });

    query.orderBy("timestamp", "desc").where((qb) => {
      if (filters.status) {
        qb.table("status").where("status", filters.status);
      }
    })

return query
}

listWithPaginate{
return list().paginate({
      currentPage: page,
      perPage: limit,
      isLengthAware: true,
    });
}

[felixmosh]

What is the db that you using? Looks like regular query... Can you reduce it an still get the error?

[noelsoong]

Mssql. It is used with a with clause. What do you mean reduce? Simplify the query? As long as it is has the .with clause there is a syntax error.

[felixmosh]

All this lib does it uses the given query as a sub-query of count query in order to calculate the total num of rows.

It doesn't build the query by hand, so this is really weird for me, I will test it with MySQL to check if I can reproduce the issue.

[felixmosh]

I've tested on a small query, in MYSQL, and it works properly.

Select count(*) as total from (with test as (SELECT * FROM `persons` where id>7) select* from test where id=10) as x;

In addition, I've added a test for this case, maybe the order of select & with is matter, try this:

const query =  this.db
      .with("cte_events", (qb) => {
        qb.from<TableEvent, EventDetail[]>({ e: "tbl_events" })
          .select(
            { id: "e.event_id" },
            { uid: "e.event_uid" },
            { name: "e.event_name" },
            { header: "ev.eventvenue_ticketheadermain" },
            {
              status: this.db.raw(`CASE
      WHEN e.event_active = 0 AND e.event_exists = 1 THEN 'draft'
      WHEN e.event_active = 1 AND e.event_exists = 1 AND ev.eventvenue_closesaledate > getdate() THEN 'published'
      WHEN e.event_active = 1 AND e.event_exists = 1 AND ev.eventvenue_closesaledate <= getdate() THEN 'past'
      ELSE 'unknown'
      END`),
            },
            { active: "e.event_active" }
          )
          .where((qb) => {
            qb.where("p.payee_uid", promoterUid);
            //Filters

            if (filters.eventName) {
              qb.andWhere("event_name", "LIKE", filters.eventName);
            }
          })
          .innerJoin<TableOrganisation>({ p: "tbl_payees" }, "e.payee_id_producer", "p.payee_id")
          .innerJoin({ ev: "tbl_eventvenues" }, "e.event_id", "ev.event_id")
          .innerJoin({ v: "tbl_venues" }, "ev.venue_id", "v.venue_id")
          .innerJoin({ tz: "tbl_timezones" }, "tz.timezone_id", "v.timezone_id")
          .innerJoin({ g: "tbl_genres" }, "e.genre_id", "g.genre_id")
          .leftJoin({ c: "tbl_cities" }, "v.city_id", "c.city_id");
      })
      .select<EventDetail[]>("*") // this moved to be after with clause
      .from<EventDetail[]>("cte_events");

    query.orderBy("timestamp", "desc").where((qb) => {
      if (filters.status) {
        qb.table("status").where("status", filters.status);
      }
    })

return query

[noelsoong]

i'll try it thanks

[noelsoong]

Hi it works i just needed to update to latest version of knex i was on 2.2.1