Feature Request: where style clauses within join

[alaarihan]

Environment

Knex version: latest
Database + version: MySQL 5.7.x
OS: Ubuntu 14.04
When I use this:

let products = await db.select({
      id: 'p.ID',
      status : 'p.post_status',
      name: 'p.post_title',
      // type: 'terms.name',
      price: 'price.meta_value',
      createdAt: 'p.post_date_gmt',
      updatedAt: 'p.post_modified_gmt'
    })
    .from({p:'wp_posts'})
    .leftJoin({price: 'wp_postmeta'},
    function() {
      this.on('p.id', '=', 'price.post_id').andOn('price.meta_key', '_regular_price')
    })

When I execute this query I get this error:

select `p`.`ID` as `id`, `p`.`post_status` as `status`, `p`.`post_title` as `name`, `price`.`meta_value` as `price`, `p`.`post_date_gmt` as `createdAt`, `p`.`post_modified_gmt` as `updatedAt` from `wp_posts` as `p` left join `wp_postmeta` as `price` on `p`.`id` = `price`.`post_id` and `price`.`meta_key` = `_regular_price` order by `post_date_gmt` asc limit 2 - Unknown column '_regular_price' in 'on clause'

I need to use a string here not a column for join

[tgriesser]

The current solution, which admittedly is very ugly is to do:

.andOn('price.meta_key', db.raw('"_regular_price"'))

We should probably add the concept of a where clause to the join object, so you could do:

  this.on('p.id', 'price.post_id').andWhere('price.meta_key', '_regular_price')

and it would be output as:

select .... 
left join `wp_postmeta` as `price` on `p`.`id` = `price`.`post_id` 
  and `price`.`meta_key` = "_regular_price" 
...

[wubzz]

Wouldn't it make more sense with something like .onValue / .andOnValue or .onVal / .andOnVal or .onEquals / .andOnEquals ? (for lack of better name)

It just sounds a bit weird to have .where / .andWhere in a join builder since it doesn't generate a where clause.

[elhigu]

.andOn('price.meta_key', db.raw('"_regular_price"'))

In 0.15.0 there is also helper for being able to write db.raw('??', '_regular_price') easier like db.ref('_regular_price') which does quoting automatically.

[wubzz]

I think in this case he wanted the actual string '_regular_price' so it should be db.raw("'_regular_price'") in Tims example instead of db.raw('"_regular_price"') which would yield a column.

Edit: My bad, I'm thinking postgres.. Perhaps "_regular_price" is valid in mysql..

[tgriesser]

@wubzz yeah I think onVal / andOnVal / orOnVal sounds like a good option, where it would have the same semantics as a query builder's where clause.

[wubzz]

I started playing around with this a bit:

Builder

qb()
        .select({
          id: 'p.ID',
          status : 'p.post_status',
          name: 'p.post_title',
          // type: 'terms.name',
          price: 'price.meta_value',
          createdAt: 'p.post_date_gmt',
          updatedAt: 'p.post_modified_gmt'
        })
        .from({p:'wp_posts'})
        .leftJoin({price: 'wp_postmeta'},
          function() {
            this
              .on('p.id', '=', 'price.post_id')
              .onVal(function() {
                this.onVal('price.meta_key', '_regular_price')
                  .andOnVal('price_meta_key', '_regular_price')
              })
              .orOnVal(function() {
                this.onVal('price_meta.key', '_regular_price')
              })
          })

Output

select "p"."ID" as "id", "p"."post_status" as "status", "p"."post_title" as "name", "price"."meta_value" as "price", "p"."post_date_gmt" as "createdAt", "p"."post_modified_gmt" as "updatedAt" from "wp_posts" as "p" left join "wp_postmeta" as "price" on "p"."id" = "price"."post_id" and ("price"."meta_key" = ? and "price_meta_key" = ?) or ("price_meta"."key" = ?)

What I don't really like is having to call .onVal() with an additional .onVal() + .andOnVal() to get it wrapped, but I guess it's the same for normal join functions, so maybe it's ok..

[wubzz]

Closing, feature merged towards next version. #2746