Lookup from view (Where clause in preview OK but not in Application)

Erik

New member

Your current DaDaBIK version​

You are using DaDaBIK version 11.7-Elba enterprise, installed on 11-06-2022 (installation code: xxxxxxxx), the latest version of DaDaBIK is 11.7-Elba released on 08-18-2022

System info​

PHP Version: 7.4.32

mysql version: 5.5.5-10.3.36-MariaDB-cll-lve

Web server: LiteSpeed

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36

URL installation: https://xxxxxxx


Case:
Table activity has a column 'type_id' int with digits assigned to it.
i assigned a Lookup on 'type_id' from a view table with the following WHERE clause:

`language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'type_id'
This should give me just the keywords that i have intended for this table in the language 1.

This works fine in the form preview, it only shows the description for the Data queried with the clause in just 1 language.
I have 3 languages in the View table, and just want to select one language from it.

After saving the values, i exit admin and go back to the application table activity and i see every record in the activity table is repeated 3 times, and all 3 are with the different language in the 'type_id' column.

The Lookup 'join' is, so it seems, not filtered on the language '1' as it is in the form preview.

Perhaps i do not understand the concept quite well and miss something in the config here.

Best regards
Erik
 

Erik

New member
Debugging showed the SQL Query in the application.
Here there is no sign of the WHERE clause that you can specify in the 'Field type specific settings'

As stated in my previous 'initial' message, the Lookup list itself is correct when adding a record or in preview mode.
 

Erik

New member
The systems produces:

Your SQL query ( FOR debugging purpose) :
SELECT
`activity`.`id`,
`activity`.`contact_id`,
`contact____1`.`givenName` AS `contact____givenName____1`,
`contact____1`.`middleName` AS `contact____middleName____1`,
`contact____1`.`surName` AS `contact____surName____1`,
`activity`.`project_id`,
`activity`.`date`,
`activity`.`start_time`,
`activity`.`end_time`,
`activity`.`type_id`,
`KeywordCategoryOverview____1`.`description` AS `KeywordCategoryOverview____description____1`,
`activity`.`action`,
`activity`.`discipline_id`,
`KeywordCategoryOverview____2`.`description` AS `KeywordCategoryOverview____description____2`,
`activity`.`status_id`,
`KeywordCategoryOverview____3`.`description` AS `KeywordCategoryOverview____description____3`,
`activity`.`location_id`,
`activity`.`location_id_from`,
`activity`.`location_id_to`,
`activity`.`distance`,
`activity`.`create_timestamp`,
`activity`.`update_timestamp`
FROM
`activity`
LEFT JOIN `contact` AS `contact____1` ON `activity`.`contact_id` = `contact____1`.`contact_id`
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____1` ON `activity`.`type_id` = `KeywordCategoryOverview____1`.`Keyword_id`
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____2` ON `activity`.`discipline_id` = `KeywordCategoryOverview____2`.`Keyword_id`
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____3` ON `activity`.`status_id` = `KeywordCategoryOverview____3`.`Keyword_id`
ORDER BY
`activity`.`date` ASC,
`activity`.`id`

But it should be:

SELECT
`activity`.`id`,
`activity`.`contact_id`,
`contact____1`.`givenName` AS `contact____givenName____1`,
`contact____1`.`middleName` AS `contact____middleName____1`,
`contact____1`.`surName` AS `contact____surName____1`,
`activity`.`project_id`,
`activity`.`date`,
`activity`.`start_time`,
`activity`.`end_time`,
`activity`.`type_id`,
`KeywordCategoryOverview____1`.`description` AS `KeywordCategoryOverview____description____1`,
`activity`.`action`,
`activity`.`discipline_id`,
`KeywordCategoryOverview____2`.`description` AS `KeywordCategoryOverview____description____2`,
`activity`.`status_id`,
`KeywordCategoryOverview____3`.`description` AS `KeywordCategoryOverview____description____3`,
`activity`.`location_id`,
`activity`.`location_id_from`,
`activity`.`location_id_to`,
`activity`.`distance`,
`activity`.`create_timestamp`,
`activity`.`update_timestamp`
FROM
`activity`
LEFT JOIN `contact` AS `contact____1` ON `activity`.`contact_id` = `contact____1`.`contact_id`
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____1` ON `activity`.`type_id` = `KeywordCategoryOverview____1`.`Keyword_id` AND `KeywordCategoryOverview____1`.`language_id` = '1' AND `KeywordCategoryOverview____1`.`tabel` = 'activity' AND `KeywordCategoryOverview____1`.`table_field` = 'type_id'
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____2` ON `activity`.`discipline_id` = `KeywordCategoryOverview____2`.`Keyword_id` AND `KeywordCategoryOverview____2`.`language_id` = '1' AND `KeywordCategoryOverview____2`.`tabel` = 'activity' AND `KeywordCategoryOverview____2`.`table_field` = 'discipline_id'
LEFT JOIN `KeywordCategoryOverview` AS `KeywordCategoryOverview____3` ON `activity`.`status_id` = `KeywordCategoryOverview____3`.`Keyword_id` AND `KeywordCategoryOverview____3`.`language_id` = '1' AND `KeywordCategoryOverview____3`.`tabel` = 'activity' AND `KeywordCategoryOverview____3`.`table_field` = 'status_id'
ORDER BY
`activity`.`date` ASC,
`activity`.`id`
 

eugenio

Administrator
Staff member
Hello Erik,
I don't think I have understood the context.

Are you using a VIEW as a source for a lookup field?
In which table is this lookup field?
Can you post a screenshot of the relevant form config parameters for this field?

Best,
 

Erik

New member
Hello Eugenio,

Thank you for the reply.

Yes, the VIEW is the source for the Lookupfield.
Attached the screenshot:
 

Attachments

  • 20221108 111700 Screenshot Form Config Field type_id.png
    20221108 111700 Screenshot Form Config Field type_id.png
    286.7 KB · Views: 2

Erik

New member
In the where clause:
(`language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'type_id')
 

Erik

New member
screenshot from the Application with Debug Info:
You see that i have 3 Lookup fields:
and the Where clause is adjusted for each at the end
-> .....AND `table_field` = 'type_id')
-> .....AND `table_field` = 'status_id')
-> .....AND `table_field` = 'discipline_id')

This way ONLY the Population for the Lookup Field has the Keywords that are relevant for that field.
 

Attachments

  • 20221108 112300 Screenshot Application with debug info formed SQL.png
    20221108 112300 Screenshot Application with debug info formed SQL.png
    372.6 KB · Views: 1

eugenio

Administrator
Staff member
Ok, I thought the filter (language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'type_id') was already defined in the VIEW itself, but this is not the case, correct?
So the fact that KeywordCategoryOverview is a VIEW is just incidental, do you get the same problem when you use a table as a source for a lookup field?
I am not aware of bugs with the where clause parameter.

Is Keyword_id the unique field of that view?

When you say:
"and i see every record in the activity table is repeated 3 times, and all 3 are with the different language in the 'type_id' column."
where do you see this behaviour? You should see the result of your configuration in the related select_single field (now sure how the activity table is related to this)

Best,
 

Erik

New member
'Is Keyword_id the unique field of that view?'

It is if you integrate the Language, therefor the WHERE is important, without the Where i have the same Keyword_id 3 times because it is in 3 languages. e.g 1 / 2 / 3 as language_id.

The VIEW Table is like this, see attachement.
 

Attachments

  • 20221108 113700 Screenshot KeywordCategorieOverview.png
    20221108 113700 Screenshot KeywordCategorieOverview.png
    146.4 KB · Views: 1

Erik

New member
I can change the view with this filter and check again.
But i cant see the Where Clause in the debug info in the Application screen.
Its missing from the SQL as seen in the Screenshot -> 20221108 112300 Screenshot Application with debug info formed SQL

It adds the JOIN to the Query as set in the Field type Specific settings, that is visible in the SQL Debug info.
Lookup table name: KeyCategroyOverview
Lookup table primary key field: Keyword_id
Lookup table linked fields: description

But the Where clause is not transferred to the SQL Query.
 
Last edited:

eugenio

Administrator
Staff member
What you set as "primary key" must be the primary key of the table (or what you set as unique key for the view, that typically derives from the primary key of the related table), regardless of the where clause paramter; if you set as "primary key" a field that is not primary key, it can't work correctly.

The where clause is only used to filter items in the dropdown menu.

best,
 

Erik

New member
Thanks for the clarification, now i understand that i though the WHERE Clause was intended to be integrated in the SQL query.
-> The where clause is only used to filter items in the dropdown menu.

And if i understand it correctly it is not solvable with any setting than only to adjust the view.
Now i understand the concept of the Field type specific setting 'Where clause' i could not extract it this way from the 'questionmark' quick explanation.
 

eugenio

Administrator
Staff member
If you need to use the view as source for a lookup field, you need a valid unique field so you have to redefine the VIEW (maybe using the filter you added to the where clause paramter, if this can work for you).

best,
 

Erik

New member
I understood that i have to create a view for each different combination with the filters that i put in the WHERE clause and remove this Where clause from here because it does not add anything for me in my case.

(`language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'type_id')
(`language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'status_id')
(`language_id` = '1' AND `tabel` = 'activity' AND `table_field` = 'discipline_id')


Is the WHERE clause something that would find its way to be implemented into the SQL at front in future DaDaBik versions?

I made this table because it makes it easier for me to maintain standard Lookup Keywords.
Some Keywords are used multiple times throughout my database and i can easy maintain what is shown in a lookup list.

But with separate Views it can work also, at the end it extracts the actual data from the JOINED tables on the fly.
Its just a bit more work to generate VIEWS for it each time i need a different Lookup list.
 

eugenio

Administrator
Staff member
Hello,
that where clause parameter will be only used for filtering the list of options. For the main query, apart from using a VIEW, you can add a where clause using row level custom filters.

Best,
 
Top