LimeReport Forum

General Category | Основное => Discussion | Обсуждение => Topic started by: Paul Traut on August 22, 2017, 04:44:51 PM

Title: Database table with key/value pairs
Post by: Paul Traut on August 22, 2017, 04:44:51 PM

Hello,

I have a database table like:
orderid|      key       |   value
    22     | ExpiryDate | 190606
    22     | Batch         | 123456
    22     | Product      | Test

Now I would like to see in one TextItem the value of the line where the key is "ExpiryDate" and in another TextItem the value of the line where the key is "Batch".

Hope you can understand what i mean.

Is there an easy way to do that or do i have to add for every TextItem a new datasource with the corresponding SQL query?

Paul

Title: Re: Database table with key/value pairs
Post by: Subst on August 23, 2017, 11:08:02 AM

Hi!
In what order the TextItems is on the page?

Title: Re: Database table with key/value pairs
Post by: Paul Traut on August 23, 2017, 12:20:55 PM

Hello,

at first I have the TextItem with the BatchNumber and then the one with the ExpiryDate but all in one DataBand.
I need something like "$S{$D{order.value} where $D{order.key} == "Batch"}" or similar.

Title: Re: Database table with key/value pairs
Post by: Subst on August 24, 2017, 05:25:20 AM

I see.
You need to refactoring of your database or as you known need to create datasources with appropriate queries for each textitem.
May be Alex know some other variantes

Title: Re: Database table with key/value pairs
Post by: Arin Alex on August 24, 2017, 10:48:36 PM

I can suggest to use subqueries in your sql query to combine all the values in one line.
For example:

Code: [Select]

Select (Select Value from order o where o.OrderId = order.OrderId and Key = 'Batch') Batch,
       (Select Value from order o where o.OrderId = order.OrderId and Key = 'ExpiryDate') ExpiryDate
from order
where OrderId = 22


Title: Re: Database table with key/value pairs
Post by: Subst on August 25, 2017, 05:30:14 AM

Alex, if no the one order in table this variant not appropriate. In this case is needed to create more then one query all the same.

May be using group by orderid and put the resulting datasource as datasource for band?
So we'll get the datasource like this

orderid  | expirdedate | batch
______________________________
 1       | some        | some
 2       | some        | some

I've no test this idea, but it's possiblle the solution?

Title: Re: Database table with key/value pairs
Post by: Paul Traut on August 25, 2017, 10:48:45 AM

Thank you for your answers.

I have tested the sql query and in my case I need to "group by orderid" otherwise I will get several lines (depending on the number of lines with the same orderid) with the same entries.

I don’t know Alex, but maybe in the future  there could be something like "$D{order.Value where order.Key = 'something'}" Something could although be a Variable $V{...}. Think this would give more flexibility ;)

Title: Re: Database table with key/value pairs
Post by: Arin Alex on August 25, 2017, 05:25:51 PM

Paul Traut
You can also use "distinct" instead "group by" :)
I will think about your suggestion

Title: Re: Database table with key/value pairs
Post by: Arin Alex on September 05, 2017, 12:51:48 AM

I have added the function getFieldByKeyField("Datasource name", "Value field name", "Key field name", "Key value") in development branch at github

Title: Re: Database table with key/value pairs
Post by: Paul Traut on September 21, 2017, 02:20:41 PM

Thanks!!

I will test it

Title: Re: Database table with key/value pairs
Post by: Paul Traut on December 07, 2017, 05:05:18 PM

Hello, I`m back  :D

I am trying to use the function getFieldByKeyField("Datasource name", "Value field name", "Key field name", "Key value") but the designer tells me, that he can not find the variable getFieldByKeyField. I have installed thid version "https://github.com/fralx/LimeReport.git"

Title: Re: Database table with key/value pairs
Post by: Arin Alex on December 07, 2017, 08:33:47 PM

Hi :)
You have to use Development branch at github.

Title: Re: Database table with key/value pairs
Post by: Paul Traut on December 08, 2017, 09:58:07 AM

I have tried to do so, but i failed  :-\
How can I get the Development branch?

Title: Re: Database table with key/value pairs
Post by: Arin Alex on December 08, 2017, 08:34:30 PM

Sorry i mistyped :) the branch name is "develop".
if you use git command line client, just type: git checkout develop