Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Potpot
    @pot-code

    knex.raw('? FETCH FIRST ? ROWS ONLY', [knex.('table').where('id', 1), limit])

    tyvm, it works now!

    Jakub
    @Jakub41
    Hello, may I ask your help how to convert an SQL to pure Knex as I'm new to it and have no idea how please :)
    SELECT
        *
    FROM
        "conversation"
    ORDER BY
        (
            SELECT
                max("created_at")
            FROM
                "message"
            WHERE
                "conversation_id" = 'conversation.id')
        DESC
    Kristjan
    @KristjanTammekivi
    Something like
    knex('conversation')
        .orderBy(
            knex('message')
                .max('created_at')
                .whereRaw('conversation_id = conversation.id'),
            'desc'
        )
    Jakub
    @Jakub41
    Thanks Yes but I giving me undefined column on the where part :( when using Knex.
    Also a question would better to do this with a join instead as I'm having 2 tables there message and conversation?
    Jakub
    @Jakub41
    I decided to use a join and I did in this way but I got a syntax error near "SELECT" after the join.
    Do you know why and how to fix it?
    below also the SQL generated from this query
    this.builder
          .join(
            this.tx(messageTable)
              .select(messageColumns.conversationId)
              .max(messageColumns.createdAt, 'updated_at')
              .where(messageColumns.conversationId, columns.id)
              .groupBy(messageColumns.conversationId),
            messageColumns.conversationId,
            'updated_at'
          )
    SELECT
        *
    FROM
        "conversation"
        INNER JOIN
        SELECT
            "conversation_id",
            max("created_at")
        FROM
            "message"
        WHERE
            "conversation_id" = 'id'
        GROUP BY
            "conversation_id" ON "conversation_id" = "updated_at"
    Arthur Neuman
    @exoRift
    So I have a problem
    I have this query
                    .andWhere(
                      req.db.raw('(:start::date >= starttime AND :start::date <= endtime) OR (:end::date >= starttime AND :end::date <= endtime)', {
                        start: start.toISOString(),
                        end: end.toISOString()
                      })
                    )
    The problem is, the colons that are meant to convert it to date format treat the named bindings as identifiers instead of values
    Since it's interpreted as :start: instead of :start
    Mikael Lepistö
    @elhigu
    Workaround is to use ?? bindings
    Slim Hmidi
    @slim-hmidi

    hello,
    I created a query using with and raw:

     const eventKnex = Event.knex()
          const fetchedEvents = await eventKnex
            .with('cte_event_imp', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
                from event,
                (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
                where event_type = 'imp'
                order by ST_Distance(geography, ref) asc
                limit 1
            `)).with('cte_event_click', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
            from event,
            (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
            where event_type = 'click'
            order by ST_Distance(geography, ref) asc
            limit 1
        `))
            .select(['cte_event_imp.id as imp_id', 'cte_event_click.id as click_id'])
            .from(['cte_event_imp', 'cte_event_click'])

    I got this error:

    "with cte_event_imp as (select *, ST_Distance(geography, ref)/1000 as distance from event,            (select ST_MakePoint(3, 4)::geography as ref) as ref            where event_type = 'imp'            order by ST_Distance(geography, ref) asc            limit 1        ), cte_event_click as (select *, ST_Distance(geography, ref)/1000 as distance        from event,        (select ST_MakePoint(3, 4)::geography as ref) as ref        where event_type = 'click'        order by ST_Distance(geography, ref) asc        limit 1    ) select cte_event_imp.id as imp_id, cte_event_click.id as click_id from cte_event_imp as 0, cte_event_click as 1 invalid reference of an entry in the FROM clause for the table "cte_event_imp"

    The issue is with the from(['cte_event_imp', 'cte_event_click']), when I put only one table name it works but I need to select from both of the cte. How can I fix that?

    Slim Hmidi
    @slim-hmidi

    I fixed it using the table indexes:

     const eventKnex = Event.knex()
          const fetchedEvents = await eventKnex
            .with('cte_event_imp', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
                from event,
                (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
                where event_type = 'imp'
                order by ST_Distance(geography, ref) asc
                limit 1
            `)).with('cte_event_click', eventKnex.raw(`select *, ST_Distance(geography, ref)/1000 as distance
            from event,
            (select ST_MakePoint(${lon}, ${lat})::geography as ref) as ref
            where event_type = 'click'
            order by ST_Distance(geography, ref) asc
            limit 1
        `))
            .select(['0.id as imp_id', '1.id as click_id'])
            .from(['cte_event_imp', 'cte_event_click'])

    Is it the right way to select columns of multiples tables or there is another clean way?

    Mikael Lepistö
    @elhigu
    Pass an object for .from({ cei : ’cte_event_imp’, cec: ’cte_event_click’ })
    Coop
    @Darkle
    Hi, I have a question about knex, sqlite3 and its async nature. If I have a transaction that takes multiple seconds to complete, does that mean nothing else can read/write to the db, or are other parts of my code still able to read/write to the database becase the sqlite3 library is async?
    Mikael Lepistö
    @elhigu
    if sqlite library supports multiple connections to the database, reading while transaction is open should work (years ago sqlite didn't support multiple connections, but I think it supports them now).
    you also need to configure knex to have poolsize > 1 in that case
    Arthur Neuman
    @exoRift
    Would it be possible to automatically replace all instances of selecting a specific column (interval (Postgres)) to extract epoch?
    Could it be done with wrapIdentifier?
    Arthur Neuman
    @exoRift
    Basically, is there any better way to do this
    db('meetings')
        .select(['*', db.raw('EXTRACT(EPOCH from length) as length')])
        .where('id', '1')
        .orderBy('startdate', 'asc')
    Mikael Lepistö
    @elhigu
    That looks the way it should be done 👍👌
    Wigger Boelens
    @Boelensman1
    Hi! Is there anyone who could quickly walk me through how knex acquires a connection and if I want to make changes to that where I should put the tests for that. Want to make a pull request to allow mode selection for the sqlite connection, but having some trouble familiarizing myself with the codebase
    Mikael Lepistö
    @elhigu
    There is no easy way to implement that kind of funcionality... also how would you like to work exactly. you are probably better off by creating separate knex instances ro/rw connections (if that is what you mean vy mode selection).
    There is also an issue / feature request or somthing like that in knex issues with more discussion about that kind of feature.
    Wigger Boelens
    @Boelensman1

    There is no easy way to implement that kind of funcionality... also how would you like to work exactly. you are probably better off by creating separate knex instances ro/rw connections (if that is what you mean vy mode selection).

    What I'm looking for is the option to pass the OPEN_URI option to sqlite3

    Which allows you to, among other things, use named in-memory databases, which in turn allows you to use a pool and transactions on in memory databases
    node-sqlite implements it, so (in theory) it should be possible to replace new this.driver.Database( this.connectionSettings.filename, with new this.driver.Database(this.connectionSettings.filename, this.connectionSettings.mode (or something)
    Wigger Boelens
    @Boelensman1
    At least that's what I'm hoping
    https://github.com/mapbox/node-sqlite3/pull/1078/files is the pull request adding support for it in node-sqlite3

    There is also an issue / feature request or somthing like that in knex issues with more discussion about that kind of feature.

    I can't seem to find that issue, what should I search for?

    Wigger Boelens
    @Boelensman1
    I created a pull request (knex/knex#4446), so any comments on things I did wrong or you would like changed can be placed there!
    Snir Shechter
    @SnirShechter

    Hey everyone,

    I'm wondering about the best performant way to access the database very frequently.

    I have a URL shortener microservice. With each access to a short URL, I'm saving a count of that access in the database. Of course, this can lead to thousands of changes in the database each minute.

    Should I be adding some kind of cache + flush mechanism before Knex or does Knex handle it properly?

    Mikael Lepistö
    @elhigu
    Knex can handle thousands of queries per second if the underlying db can. Though I’m not sure if an SQL database is the best for that use case.
    Snir Shechter
    @SnirShechter
    So it is better for me to make some kind of cache
    I'll flush it every 10~ seconds as a single operation to the database
    It's just count++ :)
    Mikael Lepistö
    @elhigu
    Well i would just write every operation separately and optimize afterwards if there really is a problem. With that caching you will risk loosing data if app crashes.
    If loosing some data is not a problem and good write performance is the only goal I would think of using redis with persistency set to flush to disk every second or so.
    Snir Shechter
    @SnirShechter
    Yes, that's a risk, but the microservice is an open-source (https://github.com/mini-services/miniurl) so I don't really want to burden the database. I don't mind losing some of that count in extreme cases.
    I agree, but as an open-source, I can't really set those constraints.
    Mikael Lepistö
    @elhigu
    You can setup a separate db for its data to not to burden other databases
    Snir Shechter
    @SnirShechter
    Already done as part of the Helm chart
    But a user can just connect it to an existing database
    I think caching the updates just for the count operation would probably be fine.
    Mikael Lepistö
    @elhigu
    If you dont know what kind of loads users put to that service and to what kind of db it connects i suppose it should be configurable how many requests are queued at most and for how long maximum time
    If you really want to add that cache at all :j
    Snir Shechter
    @SnirShechter
    You're right. I'll make it configurable :)
    Chris Newhouse
    @newhouse
    Any idea the best way to add the MATERIALIZED keyword to a with / CTE in Knex? I need WITH "foo" AS (...) to become WITH "foo" AS MATERIALIZED (...).
    2 replies
    donaldball
    @donaldball_twitter
    I am on a quest to excise deadlock errors from a set of services I have inherited and am wondering if I'm missing something? I don't see any facility in knex to retry deadlock errors. I do see two mysql dialect libraries which mix that functionality in. Is that the recommended approach? Is there any interest in a common way to express this?
    Phong Thieu
    @pthieu

    Hi, I'm trying to query a table1 with a column that is an array of UUIDS:

    table1 {
      ...
      uuids: UUID[],
      ...
    }

    I want to take an input array of uuids and query on this column using the ANY operator, as in if any one of the UUIDs within the input array exists in any of the column's UUIDs, then the record is returned.

    I have this piece of code

    knex(TABLE_NAME).where('uuids', '@>', uuidsWanted);

    But what happens here is that the column uuid must be exactly what is listed in uuidsWanted. Is there a way to do this with knex or would

    I have to do this with a raw query?

    I've tried as well but I believe the knex.raw syntax is wrong

    return knex(TABLE_NAME).where('uuids', '@>', knex.raw(`ANY(${mmUuids})`));
    Phong Thieu
    @pthieu
    Update: I was able to do it like this
    return knex(TABLE_NAME).where(
        'uuids',
        '&&',
        knex.raw(`ARRAY[${uuidsWanted.map((id) => `'${id}'`)}]::UUID[]`),
      );