Dr. Sauce Comments - Answer Overflow

Dr. Sauce

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  const queryWithSelect = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await queryWithSelect.execute();

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  const queryWithSelect = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await queryWithSelect.execute();

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

Can confirm both of these work 🙂 Thank you! Makes sense now

let queryBuilder;

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    )
    .select([
      /** Mapping */
      'mapping.id',
      'mapping.distance',
      'mapping.created_at',
      'mapping.updated_at',
      /** Source disease */
      'src_disease.id as src_disease_id',
      'src_disease.coding_id as src_disease_coding_id',
      'src_disease.coding_id_string as src_disease_coding_id_string',
      /** Target disease */
      'tgt_disease.id as tgt_disease_id',
      'tgt_disease.coding_id as tgt_disease_coding_id',
      'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
    ]);

  const results = await query.execute();

let queryBuilder;

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    )
    .select([
      /** Mapping */
      'mapping.id',
      'mapping.distance',
      'mapping.created_at',
      'mapping.updated_at',
      /** Source disease */
      'src_disease.id as src_disease_id',
      'src_disease.coding_id as src_disease_coding_id',
      'src_disease.coding_id_string as src_disease_coding_id_string',
      /** Target disease */
      'tgt_disease.id as tgt_disease_id',
      'tgt_disease.coding_id as tgt_disease_coding_id',
      'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
    ]);

  const results = await query.execute();

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

Thank you so much for clarifying!

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

I see, so the issue was the line:

query = query.select([
...

query = query.select([
...

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

Thanks @koskimas what's the best way to add a conditional call onto the query chain? For example, if only in specific cases I want to add an .orderBy call

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

It looks like the reassignment of query to itself was causing the issue. If I do something like this, the end result has the correct typing:

  let queryBuilder;

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  queryBuilder = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await queryBuilder.execute();

  let queryBuilder;

  const query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  queryBuilder = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await queryBuilder.execute();

The above example is slightly contrived. The reason I am using multiple statements to build the query rather than all in one block is because I have several if conditions for which certain parts of the query should or should not be included.

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

  let query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  query = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await query.execute();

  let query = db
    /** Select all disease mappings */
    .selectFrom('disease_term_similarity as mapping')
    /** Append on the source disease's info by it's ID */
    .leftJoin('disease as src_disease', (join) =>
      join.onRef('src_disease.id', '=', 'mapping.source_disease_id')
    )
    /** Append on the target disease's info by it's ID */
    .leftJoin('disease as tgt_disease', (join) =>
      join.onRef('tgt_disease.id', '=', 'mapping.target_disease_id')
    );

  query = query.select([
    /** Mapping */
    'mapping.id',
    'mapping.distance',
    'mapping.created_at',
    'mapping.updated_at',
    /** Source disease */
    'src_disease.id as src_disease_id',
    'src_disease.coding_id as src_disease_coding_id',
    'src_disease.coding_id_string as src_disease_coding_id_string',
    /** Target disease */
    'tgt_disease.id as tgt_disease_id',
    'tgt_disease.coding_id as tgt_disease_coding_id',
    'tgt_disease.coding_id_string as tgt_disease_coding_id_string'
  ]);

  const results = await query.execute();

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

Oh no, my message was too long 🙂 Here it is:

14 replies

KKysely

•Created by Dr. Sauce on 7/7/2023 in #help

Type of query result is {}[]

Hey @valtyrorn apologies, I made a mistake in simplifying my query for the example above. I edited the post to include that part. The query is all working and returning results sucessfully

14 replies

Gaming

Programming