How to include a data from a foreign column table?

[MichelDiz]

Hi, greetings

I'm using Join-Monster with join-monster-graphql-tools-adapter -Mysql (mariaDB), Knex.

My "problem" is something like do a join of a query to get a name of the product based on the ID of a product through a foreign column in another table. I was able to do it the standard way, but the data does not stay as I would like to consume, see the example below.

PS. I can live the way I'm doing if it's not possible. Or maybe I'm just lost xD

Note: Only "name" (Foreign column. Regarding the current query column) would be the focus of my question. By understanding how I could get data from "name" I turn to the rest down. All other things like "Where", "Limit" and etc are already in place.

PS. What I'm looking for would be something like this allusion.

    name: {
      type: name // I'm using apollo this crazy thing would never happen
                  //  I'm making an allusion with "Including Data From the Junction"
    },
  details: {
      sqlTable: 'Ps_product_lang',
        include: {
          name: {
            sqlColumn: 'name'
          },
            sqlJoin: {... join some foreign table}
     }
  }

First the ideal

query {
   feedByIDcategory(id_category_default: "2") {
  id
  name // This data is in another table. 
  available_date
  condition
  show_price
  date_add
  date_upd
   }   
}

The result should be this

{
  "data": {
    "feedByIDcategory": [
      {
        "id": 2,
        "name": "Some Fancy Name",
        "available_date": "2019-11-30",
        "condition": "new",
        "show_price": 1,
        "date_add": "2017-08-04",
        "date_upd": "2017-08-14"
      },

Then the current result

query {
   feedByIDcategory(id_category_default: "2") {
  id
  details {
    name
  }// This is a sub query in another table through a join. It is not desired
  available_date
  condition
  show_price
  date_add
  date_upd
   }   
}

{
  "data": {
    "feedByIDcategory": [
      {
        "id": 2,
        "details": {
         "name": "Some Fancy Name"
        },
        "available_date": "2019-11-30",
        "condition": "new",
        "show_price": 1,
        "date_add": "2017-08-04",
        "date_upd": "2017-08-14"
      },

Example of my join

              details: {
                sqlJoin: (ThisTable, Ps_product_lang) => `
                (${ThisTable}.id_product = 
                ${Ps_product_lang}.id_product AND 
                ${Ps_product_lang}.id_lang = 1 AND 
                ${Ps_product_lang}.id_shop = 1)`,
              },

I added below just by reference the SQL I use to get data (without the Join-Monster, by SequelPro)

SELECT SQL_CALC_FOUND_ROWS 
 pl.`name`  AS `name`,
 cp.`position`  AS `position`
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 INNER JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product` AND cp.`id_category` = 3);

[horakmx]

Same problem is discussed here #227 and here #311 (workaround with subselect)

[GlennMatthys]

See https://github.com/acarl005/join-monster/wiki/Proposal:-1-on-1-join where we can work out an API extension for this.

[nicoabie]

JM supports this though https://join-monster.readthedocs.io/en/latest/field-metadata/#computed-columns sqlExpr with a subquery.

Another option is for you to query from a view where you already have the data normalized.