create lucid relation with join and custom select not work in eager loading

[farshadfahimi]

I have a Product class with the relation like below

class Product extends Model {

  prices() {
  return this.hasOne('App/Models/ProductsPrice', 'id', DB.raw('`products_prices`.`products_id`'))
            .leftJoin('products_offers as po', (builder) => {
                builder.on('po.products_id', '=', 'products_prices.products_id')
                    .on('po.is_published', '>', DB.raw('0'))
                    .on(DB.raw(`NOW() BETWEEN started_at and ended_at AND
                        ( po.stock_count >= 1 OR po.stock_count is NULL )
                    `));
            })
            .select(DB.raw(`? as loyalty_level,
            products_prices.products_id as products_id,
            case po.type
                when "FIXED" then po.amount
                when "MINUS" then products_prices.price - po.amount
                else products_prices.price
            end as offer,
            products_prices.bronze_price as bronze,
            products_prices.silver_price as silver,
            products_prices.gold_price as gold,
            GREATEST(IFNULL(products_prices.market_price, 0), products_prices.price) as base,
            case 
                when po.amount IS NULL THEN "regular"
                else "special"
            end AS offer_type`, [ this._user ? this._user.loyality_level : null ])
        )
  }
}

I'm checking the query and this is fine and have no problem.
but when I'm calling this relation for eager loading. it return null like below query

Product.query().with('prices').orderBy('id', 'desc').first()

but If fetch the price it return the result like below

product.prices().fetch()

what is the problem and how should I solve this

[farshadfahimi]

As I try to reproduce the bug. found when the eager load method is call it fetch the data but after that when try to group the data the data would be drop becase it should not defined and foreign_key and set the relation to be null.

But is there any solution for resolve this?
some times we need to have a join in the relation model

[thetutlage]

Can you please enable query debugging and share the queries being executed?

[farshadfahimi]

{
  __knexUid: '__knexUid1',
  __knexTxId: undefined,
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ null, 21494, 21492, 21490, 21488, 21486 ],
  __knexQueryUid: '3498e87c-7ae2-433a-956e-36660e60fd44',
  sql: 'select ? as loyalty_level,\n' +
    '            products_prices.products_id as products_id,\n' +
    '            case po.type\n' +
    '                when "FIXED" then po.amount\n' +
    '                when "MINUS" then products_prices.price - po.amount\n' +
    '                else products_prices.price\n' +
    '            end as offer,\n' +
    '            products_prices.bronze_price as bronze,\n' +
    '            products_prices.silver_price as silver,\n' +
    '            products_prices.gold_price as gold,\n' +
    '            GREATEST(IFNULL(products_prices.market_price, 0), products_prices.price) as base,\n' +
    '            case \n' +
    '                when po.amount IS NULL THEN "regular"\n' +
    '                else "special"\n' +
    '            end AS offer_type from `products_prices` left join `products_offers` as `po` on `po`.`products_id` = `products_prices`.`products_id` and `po`.`is_published` > 0 and NOW() BETWEEN started_at and ended_at AND\n' +
    '                        ( po.stock_count >= 1 OR po.stock_count is NULL )\n' +
    '                     where products_prices.products_id in (?, ?, ?, ?, ?)'
}

this is the prices relation query executed by with

I found that the query in the BaseRelation is executed
but in the below function

async eagerLoad (rows) {
    const mappedRows = this.mapValues(rows)
    if (!mappedRows || !mappedRows.length) {
      return this.group([])
    }
    this._eagerLoadFn(this.relatedQuery, this.foreignKey, mappedRows)
    const relatedInstances = await this.relatedQuery.fetch()
    return this.group(relatedInstances.rows)
  }

when log the relatedInstances it has result. but in the last line of method when group the data
It has this result

{
  key: 'id',
  values: [ { identity: undefined, value: [ProductsPrice] } ],
  defaultValue: null
}

it seams it can not group the result. because I'm using the DB.raw('products_prices.products_id') for foreign key

[farshadfahimi]

@thetutlage
I could resolve this problem in this way. I'm using the hasOne relation so explain this problem on this class.

In the HasOne class in Lucid/Relations we have a method named group in this method use this.foreignkey in the transform for defined the the foreignKeyValue
some times we use foreignkey like products_prices.product_id or maybe DB.raw('')
in this cases the foreignKeyValue not exist in the collection, so find method didn't find any thing and return null.

I'm changing the group method like below and it work

/**
   * Takes an array of related instances and returns an array
   * for each parent record.
   *
   * @method group
   *
   * @param  {Array} relatedInstances
   *
   * @return {Object} @multiple([key=String, values=Array, defaultValue=Null])
   */
  group (relatedInstances) {
    const transformedValues = _.transform(relatedInstances, (result, relatedInstance) => {
      // add this part for find the table foreign key 
      let foreignKey = this.foreignKey
      if(foreignKey !== undefined)
        foreignKey = String(foreignKey.split('.').splice(-1, 1))

      const foreignKeyValue = relatedInstance[foreignKey]
      const existingRelation = _.find(result, (row) => row.identity === foreignKeyValue)

      /**
       * If there is already an existing instance for same parent
       * record. We should override the value and do WARN the
       * user since hasOne should never have multiple
       * related instance.
       */
      if (existingRelation) {
        existingRelation.value = relatedInstance
        return result
      }

      result.push({
        identity: foreignKeyValue,
        value: relatedInstance
      })
      return result
    }, [])
    return { key: this.primaryKey, values: transformedValues, defaultValue: null }
  }