Hops… answer to myself
SELECT id,
name,
trim(regexp_replace(line, '.*:([^:]*)', '\1')) as value_text,
trim(regexp_replace(line, '([^:]*):.*', '\1')) as display_name
FROM
(SELECT dynamic_field.id,
dynamic_field.name,
regexp_split_to_table(dynamic_field.config, '\n') AS line
FROM dynamic_field
WHERE object_type = 'Ticket'
AND name = 'Category') AS foo
WHERE line LIKE ' %’;-> will generate a table with: dynamic_field.id (to be linked with dynamic_field_value.field_id) dynamic_field.name value_text (to be linked with dynamic_field_value.value_text) display_name is the human-readable format of value_text This will allow to populate a temp. table before doing the main query and speed up the said main query. And now let’s hope that the structure in dynamic_field.config won’t change too often (this code is for OTRS 4) > On 10 Jun 2016, at 14:59, Olivier Macchioni <olivier.macchi...@wingo.ch> > wrote: > > Hello Renée, > > Thank you for your idea. > > This is definitely an option - I could even create this table automatically > at the beginning of my SQL query using WITH > <https://www.postgresql.org/docs/9.1/static/queries-with.html> or something > similar. Although doing this purely in SQL is not a super-exciting idea… > > I’ll post something here if I can make this work. > > Olivier > > > > >> On 10 Jun 2016, at 14:32, Renee B <otrs.l...@perl-services.de >> <mailto:otrs.l...@perl-services.de>> wrote: >> >> Hi, >> >> I would do it that way: >> >> * Create a new table wingo_df_values (field_id, field_key, field_value) >> with unique (field_id, field_key) >> * change the backend module for Dropdown fields where the possible values >> are stored to the new table (every time the dynamic field is changed) >> * use that new table in the SQL query >> >> - Renée >> >> Am 10.06.2016 um 14:13 schrieb Olivier Macchioni: >>> Hello dear list, >>> >>> I’m trying to export a list of tickets directly from the OTRS DB - I’m >>> computing 20 columns, there are 7 joins… the request is not that small… >>> >>> And it takes approx. 50 seconds to generate 5’000 rows I need. Knowing that >>> I will eventually need to export much more than 5’000 rows, this is not >>> acceptable. >>> >>> If I remove *only* the 4 columns where I compute the value of some >>> “Dropdown” dynamic fields linked to tickets, the times drops down to 3 >>> seconds… >>> >>> So I must be doing something wrong in the way I compute the value of >>> dynamic fields…. or maybe the design of the DB itself is suboptimal? >>> >>> The best solution I’ve found so far is a poor man’s parser on the YAML >>> content of dynamic_field.content: >>> >>> SELECT id, >>> (SELECT regexp_replace(config, '.*' || dynamic_field_value.value_text || >>> ': ([^\n]+)\n.*', '\1') >>> FROM dynamic_field_value >>> JOIN dynamic_field ON dynamic_field.id = dynamic_field_value.field_id >>> WHERE dynamic_field_value.object_id = ticket.id >>> AND object_type = 'Ticket' >>> AND dynamic_field.name = 'Category') AS "Category" >>> FROM ticket >>> WHERE ticket.tn <http://ticket.tn/> = '10479715' >>> >>> Does anyone have a better idea? >>> >>> Thanks, >>> >>> Olivier >>> >>> P.S. I’m using PostgreSQL - it would have been nicer to store the data in >>> JSON format, which is natively supported, it could have save some precious >>> CPU cycles but well... >>> >>> >>> --------------------------------------------------------------------- >>> OTRS mailing list: otrs - Webpage: http://otrs.org/ <http://otrs.org/> >>> Archive: http://lists.otrs.org/pipermail/otrs >>> <http://lists.otrs.org/pipermail/otrs> >>> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >>> <http://lists.otrs.org/cgi-bin/listinfo/otrs> >> >> -- >> Perl / OTRS development: http://perl-services.de <http://perl-services.de/> >> OTRS AddOn repository: http://opar.perl-services.de >> <http://opar.perl-services.de/>--------------------------------------------------------------------- >> OTRS mailing list: otrs - Webpage: http://otrs.org/ <http://otrs.org/> >> Archive: http://lists.otrs.org/pipermail/otrs >> <http://lists.otrs.org/pipermail/otrs> >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> <http://lists.otrs.org/cgi-bin/listinfo/otrs> > --------------------------------------------------------------------- > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
signature.asc
Description: Message signed with OpenPGP using GPGMail
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
