MintJoomlaMintJoomla

Advanced Records field - Custom SQL query

Idea Idea | Solved Solved | Cobalt 7 | Hits: 1483 | Comments: 7 | Bookmarked: 0 | Followers: 0

pepperstreet VIP
Total posts: 3,837
25 Mar 2012 14:03

(This topic is related to my older "un-reviewed" feature request on ME Support area)

Recently, I came across some interesting custom SQL fields. You can find such fields in CommunityBuilder (Incubator project, 3rd Party by SoftForge.co.uk), FlexiContent, Seblod2 (dynamic selectbox) and ContactEnhanced. Some have a helping GUI. Some have a simple input textarea.

They allow to enter custom SQL queries! A great feature if you need to pull data from other tables in the internal DB. (e.g. CB Querie field supports external DB tables)

More or less Resources1.5 does something similar in the Records-field, but with pre-defined queries via a select-box. It would be nice to have a custom query field as a seperate field plugin or a new advanced Cobalt-Records-field.

Technically, it might have different working modes, because of performance reasons. IMHO, a good example is SoftForge CB MySQL field. I like most the different modes: display, save, both! Also, entering the query is made simple by multiple input fields.

FlexiContent has an interesting approach: Each Select/MultiSelect field has an option to use a Custom SQLquery.

The ContactEnhanced component has a query input field, which seems to generate different markup on output: Multiple values become a selectbox in frontend.

Possible use-cases and benefits:

  • display data from different components (internal, external DB)
  • fill submission form elements with dynamic values
  • save and update other tables and fields (integration with other extensions)

Thanks for listening.

Last Modified: 02 Mar 2014

Official Answer

This is great Idea. I think we can add "Source" group for Checkbox, radio, select, multiple select fields and source van be text area as before and SQL query.

That is exited.

And I am so glad you people so active here. To be true you hold me up on positive tone. :D

Quote

Sackgesicht VIP
Total posts: 1,636
25 Mar 2012 15:43

it could also be used as an export tool ...

I use SQL2EXCEL to export resources data to a csv/Excel file for use in other applications. Since the project seems on hold it would be good to have something for Cobalt/Joomla 2.5

Quote

Sackgesicht VIP
Total posts: 1,636
25 Mar 2012 16:09

SQL2EXCEL will only work 1 way -- as export tool.

Mini tutorial: .. :) :

Use a SQL quert like the following to export a type to an csv/Excel File :

SELECT  a.title as `Title`, 

        MAX(IF(b.field_id=1,b.field_value, NULL)) AS 'Field 1',

        MAX(IF(b.field_id=4,b.field_value, NULL)) AS 'Field 2',

        MAX(IF(b.field_id=2,DATE_FORMAT(b.field_value, '%d-%b-%Y'), NULL)) AS 'Date 1',

        MAX(IF(b.field_id=3,DATE_FORMAT(b.field_value, '%d-%b-%Y'), NULL)) AS 'Date 2'

FROM jos_js_res_record a 

LEFT JOIN jos_js_res_record_values b ON a.id=b.record_id

WHERE a.categories LIKE '%"Active"%'

GROUP BY a.id

ORDER BY a.title ASC

This will export 4 fields (incl. 2 date fields) of a Resources 1.5.9 Type with a filter of a specific category ("Active").

PS:

For performance reasons i had to add another index to the database .. :)

Quote

pepperstreet VIP
Total posts: 3,837
26 Mar 2012 12:01
Quote

andiamine VIP
Total posts: 295
26 Mar 2012 18:08

Yes custom Query is needed too, like in RSForm Pro

Quote

Jacques_v
Total posts: 1
12 Apr 2012 05:14

See also this topic

Quote

Sackgesicht VIP
Total posts: 1,636
28 Jul 2012 05:51

Here an updated version which i use in Cobalt to export the fields in a nice way ....

Select `title` AS `Title`,

(select `title` from `xxxx_js_res_record` 

    where (`id` = 

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 25) and (`record_id` = `a`.`id`))))) AS `Field1`,

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 1) and (`record_id` = `a`.`id`))) AS `Field2`,

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 2) and (`record_id` = `a`.`id`))) AS `Field3`,

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 3) and (`record_id` = `a`.`id`))) AS `Field4`,

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 5) and (`record_id` = `a`.`id`))) AS `Field5`,

(select `field_value` from `xxxx_js_res_record_values` where ((`field_id` = 27) and (`record_id` = `a`.`id`))) AS `Field6`rom `xxxx_js_res_record` `a` where (`categories` like '%"BlaBlaBla"%') order by `title`
Quote

pepperstreet VIP
Total posts: 3,837
22 Aug 2012 13:30
Powered by Cobalt

Login (copy)