Solved

sql generated in explorer is weird

  • 18 January 2023
  • 4 replies
  • 23 views

Hello, 

 

In lookml i have one explorer :

Example : 

explore: toto{

  join: tata{
    type: left_outer
    relationship: one_to_many
    sql_on: ${toto.id} = ${tata.id} ;;

  }

}

why in explorer when i select only one field1 from tata table the sql generaled use toto ? something like :
select filed1 from toto left join tata on toto.id = tata.id
or for me i just need this sql : 

select field1 from tata

Thank you for your response. 

Best regards

 

icon

Best answer by Dawid 19 January 2023, 11:49

View original

4 replies

Userlevel 7
Badge +1

Because “toto” is the base view for the explore. The join is just that - something that joins to the base explore based on a key. The way Looker builds SQL is that it doesn’t change the base table even if you use a field only from a join.

Thanks you Dawid, 
so if i have table1 join table2 and table2 join table3...and table9 join table10 and i want in explorer to see fields from table10 looker will generate all joins from table1 to table10 ? 

because with a complex model with several tables, it may come back heavy when loading data. no ?

I wonder if it is good practice to use lookml ? 
Thank you

Userlevel 7
Badge +1

When you have a SQL like this:

 

SELECT

  table_b.field_at

FROM table_a

    LEFT JOIN table_b USING(some_id)

 

Then your base table is still table_a.

 

Looker creates the JOINs dynamically but the base is always the same. It will only generate JOINs it needs but the base needs to be there, so in your case it will generate table1 and table10

thank you so much 

Reply