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
Your exchange_orders view looks like a PDT, doesn’t it?
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
}
Try using this field for the filter ${seller_order_links.order_group_id}
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
Then you would have to flip the views and make the inner view your base and then join to the other one