filter+databasejoin

[s6930064]

If element type databasejoin and filter type field
Then you can filter only by value NOT by label.

I think the correct it would be to filter by label not value.
see blog

 

[oreolana]

If element type databasejoin and filter type field
Then you can filter only by value NOT by label.

I think the correct it would be to filter by label not value.

If I write a query into database mysql then I use value.

But in browser I see filter as label and to filter by label.
That's all.

 

[s6930064]

In fabrik databasejoin elements, you can configure KEY ? LABEL pair.

Key is value you want to be saved in database and label is what user see in frontend.

The problem is in filter type ?field?.

It does not use label but key field to do the search.

Users do not know key values, they know label values.

 

[troester]

I can't replicate this.
It's filtering on the labels on my site.

Which fabrik version are you running?
What are your element settings?

 

[s6930064]

Have you set filter type = field ?

Here is my setup :

1. Joomla 2.5.7, Fabrik-fabrik-3.0.6.3-207-gee697dd.zip, Chrome 22 (incognito mode)
2. table2.parent_id databasejoin to table1.id
3. table = table1, value = id, label = car_names
4. and in filter type = field

now in frontend if I filter by parent_id = ?Audi? in fabrikdebug table:mergeJoinedData get ids, I get

SELECT DISTINCT?.. AND ( `table2 `.`parent_id` = 'Audi' )

As you see, if I want to filter by label, the AND should be table1.car_names=?Audi?.
As it is now, it filter by ids not labels.

 

[troester]

AND should be table1.car_names=?Audi?.

Yes, that's exactly what I get (in FF and Chrome).
Strange.
Do you have any prefilter set?
This issue I've seen if using a prefilter on a dbjoin element (where it's not such a big problem because as admin you'll know the value, so you can take element(raw))

 

[s6930064]

I had a prefilter for user id and a second databasejoin element. I removed them but still the same problem :

SELECT SQL_CALC_FOUND_ROWS DISTINCT `table2`.`parent_id` AS `table2___parent_id_raw`, `table1`.`car_name` AS `table2___parent_id`
FROM `table2` LEFT JOIN `table1` AS ` table1` ON ` table1`.`id` = ` table2`.`parent_id` WHERE ( ` table2`.`parent_id` = `Audi` )


The correct sql should be :

SELECT SQL_CALC_FOUND_ROWS DISTINCT `table2`.`parent_id` AS `table2___parent_id_raw`, `table1`.`car_name` AS `table2___parent_id`
FROM `table2` LEFT JOIN `table1` AS ` table1` ON ` table1`.`id` = ` table2`.`parent_id` HAVING ( ` table2___parent_id ` = `Audi` )

OR

SELECT SQL_CALC_FOUND_ROWS DISTINCT `table2`.`parent_id` AS `table2___parent_id_raw`, `table1`.`car_name` AS `table2___parent_id`
FROM `table2` LEFT JOIN `table1` AS ` table1` ON ` table1`.`id` = ` table2`.`parent_id` WHERE ( ` table1`.`car_name` = `Audi` )

ps : MySQL version 5.5.25

 

[s6930064]

Friendly bump

 

[oreolana]

I'm from Ukrane. Maybe my English isnot perfect.

Look, I have the same databasejoin type filter by label. And it's work OK!

But I use versions:
Joomla! 2.5.6 Stable [ Ember ] 19-June-2012 14:00 GMT
Fabrik 3.0.6 - http://fabrikar.com/blog/76-fabrik-306-out-now
more informations about fabrik versions below:
(Content - Fabrik Site Plugin 3.0.6 August 2012 Pollen 8 Design Ltd content 10013
Fabrik Administrator Component 3.0.6.1 August 2012 Fabrik N/A 10046
Fabrik Cron - email Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_cron 10014
Fabrik Cron - geocode Site Plugin 3.0.6.1 August 2012 Hugh Messenger fabrik_cron 10167
Fabrik Cron - Gmail Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_cron 10168
Fabrik Cron - ImportCSV Site Plugin 3.0.6.1 August 2012 Mike Griggs fabrik_cron 10169
Fabrik Cron - rest Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_cron 10171
Fabrik Cron - run PHP Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_cron 10015
Fabrik Element - access Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_element 10172
Fabrik Element - birthday Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_element 10173
Fabrik Element - button Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_element 10016
Fabrik Element - calculation Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_element 10174
Fabrik Element - captcha Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10175
Fabrik Element - cascading dropdown Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10176
Fabrik Element - checkbox Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10017
Fabrik Element - colour picker Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10177
Fabrik Element - database join Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10018
Fabrik Element - date Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10019
Fabrik Element - display text Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_element 10020
Fabrik Element - dropdown Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10021
Fabrik Element - Facebook comment Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10180
Fabrik Element - Facebook like box Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10182
Fabrik Element - Facebook like button Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10181
Fabrik Element - Facebook recommendations Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10183
Fabrik Element - field Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10022
Fabrik Element - file upload Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10023
Fabrik Element - folder Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10184
Fabrik Element - google map Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10024
Fabrik Element - google o meter Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10185
Fabrik Element - image Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10025
Fabrik Element - internal id Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10026
Fabrik Element - IP address Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_element 10186
Fabrik Element - JSPeriodical Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10187
Fabrik Element - Kaltura video upload Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10188
Fabrik Element - link Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10027
Fabrik Element - notes Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10189
Fabrik Element - open street map Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10190
Fabrik Element - password Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10191
Fabrik Element - picklist Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10192
Fabrik Element - radio button Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10028
Fabrik Element - rating Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10193
Fabrik Element - slider Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10194
Fabrik Element - Sugar CRM ID Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10195
Fabrik Element - text area Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10029
Fabrik Element - thumbs Site Plugin 3.0.6 August 2012 Thomas Spierckel fabrik_element 10196
Fabrik Element - time only Site Plugin 3.0.6 August 2012 Jaanus Nurmoja fabrik_element 10197
Fabrik Element - timer Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10198
Fabrik Element - timestamp Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10199
Fabrik Element - twitter profile Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10200
Fabrik Element - user Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10030
Fabrik Element - User group Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10201
Fabrik Element - video Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10202
Fabrik Element - yes/no Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10203
Fabrik Element - YouTube video Site Plugin 3.0.6 August 2012 peamak fabrik_element 10204
Fabrik Form - autofill Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10205
Fabrik Form - clone Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_form 10206
Fabrik Form - comment Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10207
Fabrik Form - confirmation Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10208
Fabrik Form - create Joomla user Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10211
Fabrik Form - email Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10031
Fabrik Form - Firefox Contacts Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10210
Fabrik Form - kunena forum Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10212
Fabrik Form - logs Site Plugin 3.0.6 August 2012 Peamak fabrik_form 10214
Fabrik Form - Mailchimp Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10215
Fabrik Form Module Site Module 3.0.6.1 August 2012 Rob Clayburn N/A 10010
Fabrik Form - notification Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10216
Fabrik Form - paginate Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10217
Fabrik Form - Paypal processor Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10218
Fabrik Form - Paypal Subscriptions Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10223
Fabrik Form - Ping.fm Site Plugin 3.0.6 August 2012 Thomas Spierckel fabrik_form 10219
Fabrik Form - process image exif info Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_form 10209
Fabrik Form - receipt Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10033
Fabrik Form - redirect Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10034
Fabrik Form - rest Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10220
Fabrik Form - Run PHP Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10032
Fabrik Form - Salesforce Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10221
Fabrik Form - SMS Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10222
Fabrik Form - Twitter Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_form 10224
Fabrik Form - vBulletin forum Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_form 10225
Fabrik List - copy Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10035
Fabrik List - CSV import Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_list 10231
Fabrik List - download Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_list 10227
Fabrik List - email Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_list 10228
Fabrik list - Example Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10230
Fabrik List - filter view Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10229
Fabrik List - inline edit Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10070
Fabrik list Module Site Module 3.0.6.1 August 2012 Rob Clayburn N/A 10011
Fabrik list Module Administrator Module 3.0.2 {createdate} Rob Clayburn N/A 10164
Fabrik List - Order Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10232
Fabrik List - PHP Events Site Plugin 3.0.6 August 2012 Mauro H. Leggieri fabrik_list 10233
Fabrik List - radius Search Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10234
Fabrik List - run PHP Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10036
Fabrik List - update Column Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10235
Fabrik List - Webservice Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_list 10236
Fabrik Package Site Package 3.0.5.1 April 2012 Fabrik N/A 10047
Fabrik Validation - are unique values Site Plugin 3.0.6 August 2012 Lieven Gryp fabrik_validationrule 10238
Fabrik Validation - email exists in Joomla Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_validationrule 10239
Fabrik Validation - greater than or less than Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10037
Fabrik Validation - is alphanumeric Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10240
Fabrik Validation - is email Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10241
Fabrik Validation - is not Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_validationrule 10242
Fabrik Validation - is numeric Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_validationrule 10243
Fabrik Validation - is unique Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10244
Fabrik Validation - not empty Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10038
Fabrik Validation - PHP Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_validationrule 10039
Fabrik Validation - regex Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10040
Fabrik Validation - special chars Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10245
Fabrik Validation - user exists Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_validationrule 10246
Fabrik visualization Administrator Module 3.0 Alpha Aug 2011 Rob Clayburn N/A 10165
Fabrik Visualization - approvals Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10247
Fabrik Visualization - calendar Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10041
Fabrik Visualization - chart Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10042
Fabrik Visualization - coverflow Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10248
Fabrik Visualization - fusion_gantt_chart Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10249
Fabrik Visualization - googlemap Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10043
Fabrik Visualization - Kaltura Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10251
Fabrik Visualization - media Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_visualization 10044
Fabrik Visualization - slideshow Site Plugin 3.0.6 August 2012 Hugh Messenger fabrik_visualization 10045
Fabrik Visualization - timeline Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10252
plg_fabrik_cron_gcalsync Site Plugin 3.0.6.1 August 2012 Hugh Messenger fabrik_cron 10166
plg_fabrik_cron_notifications Site Plugin 3.0.6.1 August 2012 Rob Clayburn fabrik_cron 10170
plg_fabrik_element_count Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10178
plg_fabrik_element_fbactivity Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_element 10179
plg_fabrik_form_limit Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_form 10213
plg_fabrik_validationrule_askismet Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_validationrule 10237
plg_fabrik_visualization_fusionchart Site Plugin 3.0.6 August 2012 Rob Clayburn fabrik_visualization 10250
Search - Fabrik Site Plugin 3.0.6 August 2012 Rob Claybrun search 10253
System - Fabrik Site Plugin 3.0.6 August 2012 Fabrikar.com system 10012
System - Fabrik Scheduled Tasks Site Plugin 3.0.6 August 2012 Pollen 8 system 10254
Joomla! 2.5.6
)
because I don't have ploblems with this version of fabrik.
When I updated fabrik to 3.0.6.3 lastest - there are was problens with databasejoin element plugin, with fileupload element plugin, with email element validation.

That's why I downgrade to version 3.0.6 (some plugins 3.0.6.1).

Then,
I will write my settings of element databasejoin and filter type Field (it's work at me, but usually I use filter type dropdown).

Look at the pictures below. And look next my post.

Fiersterfull, try without prefilters

 

[oreolana]

explanation.

I have table1.reporting_quarter type databasejoin with filter type Field.
it's like your table2.parent_id databasejoin.

Then important options of this table1.reporting_quarter is:

Render join as: drop-down.
Connection: site database
Table: dbme_reporting_period - like your table1
Value: id - like your table1.id
Label: quarter_full_name - like your table1.car_names

I also check Apply where when : Both
Show please select: No

My settings for filter type field:
look at the picture "5-element databasejoin by name - reporting_quarter - filter options.JPG"

And filter is working. Try this.

 

[oreolana]

And if you want to use a pre-filter. filtered records, where table2___parent_id = 'Audi'???

it must be in List -> Data -> Pre-Filter:
Field: table2___parent_id (label of this element)
Condition: equals
Value: 'Audi'
Type: Text
Apply to: your usergroup. (like Public).

I have a question.
Do you want to have table2___parent_id = Null.

Because if you use syntax:

FROM `table2` LEFT JOIN `table1` AS ` table1` ON ` table1`.`id` = ` table2`.`parent_id`

It's mean, that table2.parent_id can be equal value of table1.id and can be equal Null.
Do you want this?
because look and read: http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

on russian - great example:
http://www.intuit.ru/department/database/sql/class/free/5/2.html

 

[s6930064]

ADVANCED SEARCH + databasejoin elements

My filter problem is with ADVANCED SEARCH + databasejoin elements, not simple search. Simple search works ok.

I did a clean install (joomla 2.7 + pkg_fabrik_3.0.6.3.zip) and same problem with fabriks sample data

when I use filter (in advanced serach) , with fabrikdebug in list GetData I get wrong sql =?WHERE ( `table2`.`parent_id` = 'Audi' ).

 

[s6930064]

Friendly bump

 

[oreolana]

Advanced search + databasejoin work at me. I look this filters not like field. I look this filters like dropdown.

Maybe, when you created databasejoin element, you must choose option "Render join as" = "Drop-down" ?????

 

[s6930064]

i still have the problem with element databasejoin (render as dropdown) and filter as field in adv. search.

for now i switch to simple search, until it is fixed.

 

[oreolana]

Look, Please, install version 3.0.6 (3.0.6.1) NOT 3.0.6.3!!!!!!!
With 3.0.6.3 I also have problems. Element type databasejoin not connecting to database correctly. And element type Fileupload not work correctly.

I have joomla 2.5.6 with fabrik 3.0.6 (official release).
http://fabrikar.com/blog/76-fabrik-306-out-now

the newest does not mean the highest quality

Read my post with configuration early.

 

[s6930064]

Friendly bump...