databasejoin and multiselect dropdown issue

[mxmauro]

Hi,

I have a table named "Profesionales" with a field named "Tipo" which is a multiselect dropdown databasejoin field that links with table "Profesionales_categorias", field "id".

I want to add a "fabrik list" menu item that lists only those records where "Tipo" = 2 were selected so I add a prefilter to the "Tipo" field and set "EQUALS / 2 / NoQuote".

This is the result I cannot resolve (see the last WHERE is the issue):

getData:Unknown column 'jl2_fb_profesionales_repeat_tipo.tipo' in 'where clause' SQL=

SELECT SQL_CALC_FOUND_ROWS DISTINCT
  `jl2_fb_profesionales`.`id` AS `jl2_fb_profesionales___id`,
  `jl2_fb_profesionales`.`id` AS `jl2_fb_profesionales___id_raw`,
  (SELECT GROUP_CONCAT(jl2_fb_profesionales_categorias.descripcion SEPARATOR '//..*..//') FROM jl2_fb_profesionales_repeat_tipo LEFT JOIN jl2_fb_profesionales_categorias ON jl2_fb_profesionales_categorias.id = jl2_fb_profesionales_repeat_tipo.tipo WHERE parent_id = `jl2_fb_profesionales`.`id`) AS jl2_fb_profesionales_repeat_tipo___tipo,
  (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM jl2_fb_profesionales_repeat_tipo WHERE parent_id = `jl2_fb_profesionales`.`id`) AS `jl2_fb_profesionales_repeat_tipo___tipo_raw`,
  (SELECT GROUP_CONCAT(tipo SEPARATOR '//..*..//') FROM jl2_fb_profesionales_repeat_tipo WHERE parent_id = `jl2_fb_profesionales`.`id`) AS jl2_fb_profesionales_repeat_tipo___tipo_id,
  (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM jl2_fb_profesionales_repeat_tipo WHERE parent_id = `jl2_fb_profesionales`.`id`) AS `jl2_fb_profesionales_repeat_tipo___params`,
  `jl2_fb_profesionales`.`ocupacion` AS `jl2_fb_profesionales___ocupacion`,
  `jl2_fb_profesionales`.`ocupacion` AS `jl2_fb_profesionales___ocupacion_raw`,
  `jl2_fb_profesionales`.`nombre` AS `jl2_fb_profesionales___nombre`,
  `jl2_fb_profesionales`.`nombre` AS `jl2_fb_profesionales___nombre_raw`,
  `jl2_fb_profesionales`.`descripcion` AS `jl2_fb_profesionales___descripcion`,
  `jl2_fb_profesionales`.`descripcion` AS `jl2_fb_profesionales___descripcion_raw`,
  `jl2_fb_profesionales`.`direccion` AS `jl2_fb_profesionales___direccion`,
  `jl2_fb_profesionales`.`direccion` AS `jl2_fb_profesionales___direccion_raw`,
  `jl2_fb_profesionales`.`telefono` AS `jl2_fb_profesionales___telefono`,
  `jl2_fb_profesionales`.`telefono` AS `jl2_fb_profesionales___telefono_raw`,
  `jl2_fb_profesionales`.`email` AS `jl2_fb_profesionales___email`,
  `jl2_fb_profesionales`.`email` AS `jl2_fb_profesionales___email_raw`,
  `jl2_fb_profesionales`.`website` AS `jl2_fb_profesionales___website`,
  `jl2_fb_profesionales`.`website` AS `jl2_fb_profesionales___website_raw`,
  `jl2_fb_profesionales`.`userid` AS `jl2_fb_profesionales___userid_raw`,
  `jl2_users`.`id` AS `jl2_fb_profesionales___userid`,
  `jl2_fb_profesionales`.`image_logo` AS `jl2_fb_profesionales___image_logo`,
  `jl2_fb_profesionales`.`image_logo` AS `jl2_fb_profesionales___image_logo_raw`,
  `jl2_fb_profesionales`.`id` AS slug ,
  `jl2_fb_profesionales`.`id` AS `__pk_val`
FROM `jl2_fb_profesionales`
LEFT JOIN `jl2_users` AS `jl2_users` ON `jl2_users`.`id` = `jl2_fb_profesionales`.`userid`
WHERE ( jl2_fb_profesionales_repeat_tipo.tipo = 2 )
ORDER BY `jl2_fb_profesionales`.`nombre` ASC LIMIT 0, 10

Help will be appreciated.

Regards,
Mauro.

 

[mxmauro]

Edit: Tried adding LEFT JOINs to the list but they aren't added in the final SQL statement.

 

[cheesegrits]

Hmmm, interesting. I can see what is happening, but need to discuss with Rob the best way to resolve it.

I'm raising a github ticket on this for Rob to look at when he gets back from getting married, which I think is tomorrow.

-- hugh

 

[cheesegrits]

https://github.com/Fabrik/fabrik/issues/262

-- hugh

 

[mxmauro]

Ha ha ha, no problem.

If you need something to test, please tell me.

Regards,
Mauro.

 

[mxmauro]

Hi Rob, because I need to fix this issue, I want to contribute if possible to help finding the problem.

Can you give me some tips in where I should begin to see to try to discover the bug? (Files, functions, anything is welcome)

Regards,
Mauro.

 

[cheesegrits]

I'm having to punt this one to the 3.0.7 milestone.

-- hugh

 

[mxmauro]

Excellent.

I will expect the news.

Regards,
Mauro.

 

[rob]

hi

I've had a go at fixing this one in github - could you update from there and tell me if the changes work for you?

Cheers
Rob

 

[mxmauro]

Hi Rob,

I will do the tests and keep informed.

Thanks, Mauro.