Hi Fangbeast,
Let me pitch a SQL approach to this problem. Creating a single string out of multiple joined lines is super easy, barely and inconvenience.
What you need is SQLite's group_concact function:
https://www.sqlite.org/lang_aggfunc.html#groupconcatI'm just going to share the SQL with you. You know how to run this from your code, so the PB will only get in the way.
You can run this code using the SQLite oneline browser:
https://sqliteonline.com/To start let's create three tables with People and their marketing permissions: what check boxes they ticked when signing up.
Code:
Drop Table If Exists People;
Create Table People (
id integer,
firstName text,
lastName text
);
Drop Table If Exists MarketingChannels;
Create Table MarketingChannels (
id integer,
name text
);
Drop Table If Exists MarketingPermissions;
Create Table MarketingPermissions (
PersonId integer,
ChannelId integer
);
Insert Into People (id, firstName, lastName)
Select 1, 'Andrew', 'Hewson'
Union Select 2, 'Ben', 'Dagelish'
Union Select 3, 'Charles', 'Babbage'
Union Select 4, 'Derek', 'Brewster'
Union Select 5, 'Eugene', 'Evans';
Insert Into MarketingChannels (id, name)
Select 1, 'Post'
Union Select 2, 'Phone'
Union Select 3, 'eMail'
Union Select 4, 'SMS';
Insert Into MarketingPermissions ( PersonId, ChannelId )
Select 1, 1
Union Select 1, 2
Union Select 2, 2
Union Select 2, 3
Union Select 3, 1
Union Select 3, 3
Union Select 5, 1
Union Select 5, 2
Union Select 5, 3;
If you run this in the online browser you'll see your new tables down the left hand side ready to be queried. They'll stay there until you close or refresh the page.
What we want is to have a single text description of the person and their permissions, like this: Andrew Hewson ( Post, Phone )
Let's start with a normal join query and see why it isn't good enough:
Code:
select P.FirstName || ' ' || P.LastName || ' ( ' || C.Name || ' )' as Text
From People as P
Join MarketingPermissions as MP
On MP.PersonId = P.Id
Join MarketingChannels as C
On MP.ChannelId = C.Id
That gives us these results:
Code:
Andrew Hewson ( Post )
Andrew Hewson ( Phone )
Ben Dagelish ( Phone )
Ben Dagelish ( eMail )
Charles Babbage ( Post )
Charles Babbage ( eMail )
Eugene Evans ( Post )
Eugene Evans ( Phone )
Eugene Evans ( eMail )
That has a number of problems:
1. There's only one channel per line
2. People appear on multiple lines
3. Derek Brewster is missing because he doesn't have any channels
Let's solve these in reverse order.
Number 3 can be solved using a Left Outer Join:
https://www.w3resource.com/sqlite/sqlite-left-join.phpThe will give us an entry for Derek with NULL in the fields from the Marketing Tables. To cope with that null we'll use the IfNull function:
http://www.sqlitetutorial.net/sqlite-fu ... te-ifnull/Code:
select P.FirstName || ' ' || P.LastName || ' ( ' || ifnull(C.Name, 'None' ) || ' )' as Text
From People as P
left outer Join MarketingPermissions as MP
On MP.PersonId = P.Id
left outer Join MarketingChannels as C
On MP.ChannelId = C.Id
This gives us an extra line:
Code:
Derek Brewster ( None )
Next we need to solve the first two problems. To solve these we can use the following:
This gives us the following query. Note the ifnull that wraps the group_concat. Dealing with NULLs is always the most annoying aspect of writing SQL
Code:
select P.FirstName || ' ' || P.LastName || ' ( ' || ifnull( group_Concat( C.name, ', ' ), '' ) || ' )' as Text
From People as P
left outer join MarketingPermissions as MP
On MP.PersonId = P.Id
left outer Join MarketingChannels as C
On MP.ChannelId = C.Id
Group By firstName, lastName
This gives us these results:
Code:
Andrew Hewson ( Post, Phone )
Ben Dagelish ( PHone, eMail )
Charles Babbage ( Post, eMail )
Derek Brewster ()
Eugene Evans ( Post, Phone, eMail )
Hopefully you can see how to adapt this approach to work in your code.
I'm happy to help if you need support writing the SQL. All I ask is that you give me a script to create the tables and fill the with some sample data.
It's quite amazing what SQL can do. Especially now that it supports recursion. For example, take a look at the SQL Soduku solver and Mandelbrot set at the bottom of this page:
https://www.sqlite.org/lang_with.htmlThe combination of PureBasic and embedded SQLite is incredible. I'm thinking of putting together a tutorial series called "Powerful SQL for Pure Basic Developers." What do you think?
FAQ
Search
Register
Login
