How do I turn a single query result into a Category/Products listing?

Wappler General Need Help
1

I have a custom query that JOINs various tables of data and outputs between 1500 and 3000 lines. In a very simplified example, this is what the query outputs…

┌──────┬───────┬───────────────────────┬─────────────────┐
│ line │ co_id │        co_name        │     contact     │
├──────┼───────┼───────────────────────┼─────────────────┤
│    1 │     1 │ A.B.C Repairs         │ John Stevens    │
│    2 │     1 │ A.B.C Repairs         │ Fred Truss      │
│    3 │     2 │ Grove Bodyworks       │ James Archibald │
│    4 │     2 │ Grove Bodyworks       │ Jean Bryers     │
│    5 │     2 │ Grove Bodyworks       │ Tony Flynn      │
│    6 │     3 │ High Street Car Paint │ Peter Ahmed     │
│    7 │     3 │ High Street Car Paint │ Kash Ahmed      │
│    8 │     4 │ London Repair Shop    │ Peter Jones     │
│    9 │     4 │ London Repair Shop    │ Robert Jones    │
│   10 │     4 │ London Repair Shop    │ Heath Probert   │
│   11 │     5 │ Newport ARC           │ Steve Abraham   │
└──────┴───────┴───────────────────────┴─────────────────┘

My problem, and I am sure there is a very simple answer, is that I want the result to be printed to screen like this…

A.B.C Repairs	
	John Stevens
	Fred Truss
Grove Bodyworks	
	James Archibald
	Jean Bryers
	Tony Flynn
High Street Car Paint	
	Peter Ahmed
	Kash Ahmed
London Repair Shop	
	Peter Jones
	Robert Jones
	Heath Probert
Newport ARC	
	Steve Abraham

So the Company Name is like a Category and the Contacts are like Products. I just can’t get my head around how to set-up the Server Connect and the Repeat properties to get this result, can you throw some light on it?

2

Have a look at

1 Like
3

Use Group By.

Using repeat and nested query that Ben suggests in the shared video is inefficient as you will have to hit the sql server multiple time to get the data which you currently have from a single query.

Using group by on repeat binding expression (client side) on co_name, the datastructure you will get is an array with keys as co_name and value as array of each row which has that co_name.
You can bind the child items like $value.contact etc. And you can get the company name via $key.

2 Likes
4

Another method, using just one query, is to use a show condition - only show the company name if it’s not the same as the previous one. It’s probably the simplest method. I don’t know how it compares performance-wise (but it seems pretty quick when I’ve tried it). Eg:

dmx-show=“sc_contacts.data.qry_contacts[$index].co_name != sc_contacts.data.qry_contacts[$index-1].co_name”

1 Like
5

Hi @sid , thanks for the steer, you understood exactly what I was looking for and why, thanks.

I did a very quick trial this morning with

<div dmx-repeat:repeat1="sc_contacts_q.data.sa_contacts_q.groupBy(`business`)">
	{{$key}} 
	<div class="pl-3" id="repeat2" is="dmx-repeat" dmx-bind:repeat="$value">
		<div>{{comms}}</div></div><br>
</div>

and got

image

which is exactly what I was looking for. Not sure if this is how you intended for it to be done.

Thanks @ben, this is what I had before, it worked fine apart from the fact it took about 8 seconds to complete which felt like forever and the client was not happy :weary: On my quick test, for the same result, I am down to less than 1 second (still working on it).

image

Cheers @TomD, always good to have an alternative view on things :smile:

1 Like
6

The query is performed just the once and the manipulation is done inside the client/browser. This is very efficient for up to about 3,000 records.

7

Thats a weird hack. :smile: Will have to try it sometime.

8

Pretty much. I forgot to mention about the second nested repeat. That is correct.

9

Please correct me if I’m wrong but in my example the main query may be performed once but the three nested queries will hit each time there is a record, so for example 500 records will hit the server 1501 times. Sorry, but my knowledge of how these things actually work is almost non-existent so apologies if this is a dumb statement. Feel free to explain :smile:

10

As I said, there is one request to the server which returns all of the records according to the query. App Connect Data Traversal then takes over, manipulating the data within the browser. This method is far more efficient than manipulating the data on the server. One drawback, the efficiency disappears when a huge number of records are retrieved. Hence my statement that there should be no more than about 3,000 records.

11

My apologies, I think that I showed the incorrect video. I'll come back with the video that I intended to show.

Edit:
It looks like the video is no longer available. This is probably because there is a better method as shown in this video:

2 Likes
12

That clears things. The video you have shared is exactly what I was suggesting, and what @UKRiggers has implemented.

1 Like
13

Thanks for your time @ben, watching that video makes things clearer. The last part is very useful and gives me a broader understanding. :+1:

1 Like
14

At this stage, I have one senior moment a day, up from one moment every 3 days. fingers crossed it stays that way for a while to come. :cold_sweat:

15

You still get a lot of work done even with these small hiccups… Probably more that I work. :sweat_smile:

1 Like
16

:laughing: My whole day is one big senior moment! That, along with my 2 hour afternoon power-nap, just proves to me that I should give up and be put out to pasture. If only I could afford to do that! [He dreams!]

2 Likes