There is a way to avoid filtering at the end of the query?

[ggrmos]

Hi guys

I’m starting to use Looker, I want to create a model to be use for multiples user on his dashboards. When I use the model I declared something like:

explore: order_details{
  label: "Order Reporting"
  view_name: exchange_orders


  join: seller_order_links {
    type: inner
    sql_on: ${exchange_orders.id} = ${seller_order_links.order_group_id} ;;
    relationship: one_to_many
  }
}

This object generate a sql like:
 

WITH exchange_orders AS (select *
    from public.order_groups

    )
  ,  seller_order_links AS (select order_links.*
          from order_links
          where order_links.direct_buyer_id = 6
          AND order_links.direct_seller_id <> order_links.direct_buyer_id

          )
SELECT
    exchange_orders."source"  AS "exchange_orders.source",
    seller_order_links."order_type"  AS "seller_order_links.order_type"
FROM exchange_orders
INNER JOIN seller_order_links ON (exchange_orders."id") = (seller_order_links."order_group_id")
GROUP BY
    1,
    2
ORDER BY
    1
FETCH NEXT 500 ROWS ONLY

When I include a filter (not previously defined) I got a query like:

WITH exchange_orders AS (select *
    from public.order_groups
    where 1=1 --I'd rather the filter here (without explicit filter definition)

    )
  ,  seller_order_links AS (select order_links.*
          from order_links
          where order_links.direct_buyer_id = 6
          AND order_links.direct_seller_id <> order_links.direct_buyer_id
          AND 1=1 -- no filter on 'seller_order_links.created_at_filter'

          )
SELECT
    exchange_orders."source"  AS "exchange_orders.source",
    seller_order_links."order_type"  AS "seller_order_links.order_type"
FROM exchange_orders
INNER JOIN seller_order_links ON (exchange_orders."id") = (seller_order_links."order_group_id")
WHERE (exchange_orders."id" ) = 2
GROUP BY
    1,
    2
ORDER BY
    1
FETCH NEXT 500 ROWS ONLY

The clause:

WHERE (exchange_orders."id" ) = 2

was added at the end, I want to apply the filtering inside the first select * from public.order_groups, that it’s possible in looker?

Regards

[Dawid]

Your exchange_orders view looks like a PDT, doesn’t it?

[ggrmos]

Your exchange_orders view looks like a PDT, doesn’t it?

It’s a Derived Table like:

view: exchange_orders {
  derived_table: {
    sql: select *
    from public.order_groups
    where {% condition created_at_filter %} created_at {% endcondition %}
    ;;
  }
#  sql_table_name: public.order_groups ;;
  drill_fields: [id]
  filter: created_at_filter {
    type: date
    suggest_dimension: created_at_date
  }

... dimensions

}

[Dawid]

Try using this field for the filter ${seller_order_links.order_group_id}

[ggrmos]

Yes, but the thing is that I want to avoid create specific filter for each field that customer want to use as filter, group_id it’s just an example, in the future, anyone can drag and drop a different field to be used like a filter, in that escenario, the filter will be placed and the end of the query, and will be low performing

[Dawid]

Then you would have to flip the views and make the inner view your base and then join to the other one