Rows as Columns Pivot Table

Wappler General Need Help
1

Hi,

I want to generate the following xml using the xml export function which will get its data from a db query which uses two tables, machines and images. They are linked via a left join.

indent preformatted text by 4 spaces
<Root>
<Item>
<MakeCol>TestMake</MakeCol>
<ModelCol>TestModel</ModelCol>
<Files>
<ImageCol>image1</ImageCol>
<ImageCol>image2</ImageCol>
<ImageCol>image3</ImageCol>
</Files>
<Item>
</Root>

If I query machines and get a result for a single machine with three images I get the following result as expected;

MakeCol   ModelCol   ImageCol
-------   ---------  --------
TestMake  TestModel  image1
TestMake  TestModel  image2
TestMake  TestModel  image3

I’m not sure how I get the images to list as columns, I have read about pivot tables but I can’t seem to figure out a method to get the below.

MakeCol   ModelCol   ImageCol   ImageCol   ImageCol 
-------   ---------  --------   --------   --------
TestMake  TestModel  image1     image2     image3

The xml I am trying to copy also has the image files wrapped in a tag and again I’m not sure how this is achieved, is it a repeat of some sort?

Any suggestions or ideas would be very much appreciated.

CK

2

I’m still struggling to work out an answer to the above. I have also considered I might be able to get the result I require using a nested repeat region but can’t seem to work it out for my particular scenario.

Just to clarify what I’m trying to achieve, I need to end up with an XML output that shows;

<Root>
<Item>
<MakeCol>TestMake</MakeCol>
<ModelCol>TestModel</ModelCol>
<Files>
<ImageCol>image1</ImageCol>
<ImageCol>image2</ImageCol>
<ImageCol>image3</ImageCol>
</Files>
<Item>
</Root>

I know I need a query to list my machines make and model and then I think a repeat of the images belonging to each machine.

The machine query would have a condition to show only those machines required but I can’t figure out how I get the images per machine to list in a way the XML output would like similar to the above.

Any help would be really appreciated
CK

3

Does nobody have any suggestions as to how I might do this?

4

Could you share an example JSON of the database query you get?

6

Hi Apple

I have managed to get something close to what I need by using the Group_Concat function.

SELECT
  machinery.ref_no,
  machinery.make,
  machinery.model,
  machinery.age,
  GROUP_CONCAT(DISTINCT '<image>', 'https://mydomain.com', images.image_path, '</image>' SEPARATOR ';') AS files,
  machinery.id
FROM images
  INNER JOIN machinery
    ON images.machine_id = machinery.id
WHERE machinery.ref_no = 8888
GROUP BY machinery.id

This query gives an xml output as follows;

<machines>
<Adverts>
<ref_no>
<![CDATA[ 8888 ]]>
</ref_no>
<make>
<![CDATA[ HEIDELBERG STAHL ]]>
</make>
<model>
<![CDATA[ TH56-4.4 ]]>
</model>
<age>
<![CDATA[ 2008 ]]>
</age>
<files>
<![CDATA[ <image>https://mydomain.com/imageuploads/20180615_083945_resized.jpg</image>;<image>https://mydomain.com/imageuploads/20180615_083948_resized.jpg</image>;<image>https://mydomain.com/imageuploads/20180615_083953_resized.jpg</image>;<image>https://mydomain.com/imageuploads/20180615_083958_resized.jpg</image>;<image>https://mydomain.com/imageuploads/20180615_084002_resized.jpg</image>;<image>https://mydomain.com/imageuploads/20180615_084006_resized.jpg</image> ]]>
</files>
<id>
<![CDATA[ 35 ]]>
</id>
</Adverts>
</machines>

Somehow I would like to get the tags and their data to list/repeat inside the tags like so;

<machines>
<Adverts>
<ref_no>
<![CDATA[ 8888 ]]>
</ref_no>
<make>
<![CDATA[ HEIDELBERG STAHL ]]>
</make>
<model>
<![CDATA[ TH56-4.4 ]]>
</model>
<age>
<![CDATA[ 2008 ]]>
</age>
<files>
<image>https://mydomain.com/imageuploads/20180615_083945_resized.jpg</image>
<image>https://mydomain.com/imageuploads/20180615_083948_resized.jpg</image>
<image>https://mydomain.com/imageuploads/20180615_083953_resized.jpg</image>
<image>https://mydomain.com/imageuploads/20180615_083958_resized.jpg</image>
<image>https://mydomain.com/imageuploads/20180615_084002_resized.jpg</image>
<image>https://mydomain.com/imageuploads/20180615_084006_resized.jpg</image> 
</files>
<id>
<![CDATA[ 35 ]]>
</id>
</Adverts>
</machines>
7

Hi,

Sorry I don’t have a lot of time to analyse this. My idea would be using the Repeat step, and for each machine use the .where() formatter to find all images of that machine, so for each machine you get an array of images

// 2 database queries: machines and images
Repeat machines
    Set Value images = images.where('machine_id', '==', id)

And then comes the 2nd step of building the XML

8

I’m sorry but I don’t understand where I set in Wappler.

Set Value images = images.where(‘machine_id’, ‘==’, id)

I already tried a query with the two tables with an inner join, and I can get a result of a machine that has multiple images associated with it.

The issue is if I then group by the id of the machine to get a single row I only see one image column when what I really want are all the images shown on columns.