Need some more database info querying advice

Everything else that doesn't fall into one of the other PB categories.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Need some more database info querying advice

Post by Fangbeast »

I have an address table and I want to return everything from it. I also want to return all contact data for each record returned. The query below achieves this:

Code: Select all

Select
m.*,
n.contacttype,
n.contactdetails
FROM db_addresses As m, db_addressescontacts As n
WHERE n.recordid = m.recordid
ORDER BY titlename ASC
Of course, I get multiple hits for each record as more than one contact data item exists for each record. I wanted that.

While processing each record, I put the title, firstname, middlename and lastname into a ListIconGadget. What's the best way of gathering multiple contact data items for each record to display on the same line as each record with this sort of linked table query?

I want to display like this:

Code: Select all

(Field names: Title Firstname Middlename Lastname    Home      Work      Fax      Mobile      Email)

                     Mr   Harry       M                Houdini        20154     020202  8710    404129873 vanish@proton.net
This is the mess that I am using at the moment:

Code: Select all

CurrentLine.i = #False

;--------------------------------------------------------------------------------------------------
; Contact data structure for passing to display routine etc
;--------------------------------------------------------------------------------------------------

Structure TempContactsData
  contacttype.s
  contactdata.s
EndStructure

NewList TempContacts.TempContactsData()

;--------------------------------------------------------------------------------------------------
; Fetch all database rows from the selected table
;--------------------------------------------------------------------------------------------------

NewList RecordNumbers.s()

QueryString.s = "SELECT recordid FROM db_addresses ORDER BY recordid"
If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <>  #DatabaseQueryFail
  While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
    CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
    If CurrentRecordid.s  <>  #Empty$
      AddElement(RecordNumbers.s())
      RecordNumbers.s() = CurrentRecordid.s
    Else
      ; There was no current record id. Should never happen
    EndIf
    CurrentRecordid.s = #Empty$
  Wend
  FinishDatabaseQuery(Program\DatabaseHandle)
Else
  ; The database query failed to be executed or was empty
EndIf

;--------------------------------------------------------------------------------------------------
; Iterate through the linked list and fetch each record along with the associated contact data
;--------------------------------------------------------------------------------------------------

ForEach RecordNumbers.s()
  ;------------------------------------------------------------------------------------------------
  ; Fetch all address data from the db_addresses table for each recordid in the list
  ;------------------------------------------------------------------------------------------------
  QueryString.s = "SELECT * FROM db_addresses WHERE recordid  = '"  + RecordNumbers.s() + "'"
  If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <>  #DatabaseQueryFail
    While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
      CurrentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 0)
      If CurrentRecordid.s  <>  #Empty$
        AddElement(RecordNumbers.s())
        RecordNumbers.s() = CurrentRecordid.s
      Else
        ; There was no current record id. Should never happen
      EndIf
      CurrentRecordid.s = #Empty$
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  Else
    ; The database query failed to be executed or was empty
  EndIf
  ;------------------------------------------------------------------------------------------------
  ; Fetch all contact data for each recirdid in the list that has linked contact data
  ;------------------------------------------------------------------------------------------------
  QueryString.s = "SELECT contacttype, contactdata FROM db_addressescontacts WHERE recordid  = '"  + RecordNumbers.s() + "'"
  If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <>  #DatabaseQueryFail
    While NextDatabaseRow(Program\DatabaseHandle) <> #NoDatabaseRows
      CurrentContactType.s = GetDatabaseString(Program\DatabaseHandle, 0)
      CurrentContactData.s = GetDatabaseString(Program\DatabaseHandle, 1)
      If CurrentContactData.s  <>  #Empty$
        AddElement(TempContacts.s())
        TempContacts.s()\contacttype.s  = CurrentContactType.s
        TempContacts.s()\contactdata.s  = CurrentContactData.s
      Else
        ; There was no current record id. Should never happen
      EndIf
      CurrentRecordid.s = #Empty$
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  Else
    ; The database query failed to be executed or was empty
  EndIf
  ;------------------------------------------------------------------------------------------------
  ; Display the new string of address data, parse the list for each line
  ;------------------------------------------------------------------------------------------------
  DisaplayAddressTitleString(CurrentLine.i, ShowContacts.TempContactsData)
  ;------------------------------------------------------------------------------------------------
  CurrentLine.i + 1
  ;------------------------------------------------------------------------------------------------
Next
Amateur Radio, D-STAR/VK3HAF
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Need some more database info querying advice

Post by the.weavster »

Hi Fangles,

If I understand you correctly you want additional columns in your ListIconGadget according to the contacttypes you find and then shepherd the results to those, is that correct?

If so I'd start by creating a map of indexes for the extra columns (all this is untested):

Code: Select all

NewMap TypeIndex.i()

QueryString.s = "Select Distinct contacttype From db_addressescontacts Order By contacttype"
nIndex = 4 ; tweak this to the first column index for the additional fields
If DatabaseQuery(Program\DatabaseHandle,QueryString.s)
  While NextDatabaseRow(Program\DatabaseHandle)
    ct$ = GetDatabaseString(Program\DatabaseHandle,0)
    If ct$ <> #Empty$
      TypeIndex(ct$) = nIndex
      nIndex + 1
    EndIf
  Wend
  FinishDatabaseQuery(Program\DatabaseHandle)
EndIf
Then use that map to shepherd the contact records to the appropriate column:

Code: Select all

QueryString.s = "Select m.*, n.contacttype, n.contactdetails " + 
                "From db_addresses As m " +
                "Inner Join db_addressescontacts As n " +
                "On n.recordid = m.recordid " + 
                "Order By m.titlename"
If DatabaseQuery(Program\DatabaseHandle,QueryString.s)
  LastRecordId$ = ""
  While NextDatabaseRow(Program\DatabaseHandle)
    RecordId$ = GetDatabaseString(Program\DatabaseHandle,0)
    If RecordId$ <> #Empty$
      If RecordId$ = LastRecordId$
        ; just update the contacttype column with the contactdetails here...
      Else
        ; moved on to a new address so add a new row here...
        LastRecordId$ = RecordId$
      EndIf
    EndIf
  Wend
  FinishDatabaseQuery(Program\DatabaseHandle)
EndIf
User avatar
GedB
Addict
Addict
Posts: 1312
Joined: Fri May 16, 2003 3:47 pm
Location: England
Contact:

Re: Need some more database info querying advice

Post by GedB »

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#groupconcat

I'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: Select all

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 all

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: Select all

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.php

The 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 all

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: Select all

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 all

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: Select all

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.html

The 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?
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Need some more database info querying advice

Post by the.weavster »

Hi GedB,

You've demonstrated how to concatenate strings in SQL but you haven't demonstrated how to marshall the contactdetails into the relevant contacttype column in a ListIconGadget. So what's the next stage, using PB to chop apart and parse subsections of the string you've just concatenated?

I'm not questioning your sql knowledge but what you've posted doesn't seem to me to offer a solution to Fangle's question.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Need some more database info querying advice

Post by Fangbeast »

4 degree days doesn't make my neurons work any better but I love the code I read and when I can, I try it out on my copy (purchased!!) of SQLite Expert Professional to see if I got it right.

I haven't quite followed what Gedb showed me but it may be that I can use it in the future.

My solution as I posted it above works fine but it is just very messy to write that way and even a dopr like me can see that.
Amateur Radio, D-STAR/VK3HAF
User avatar
GedB
Addict
Addict
Posts: 1312
Joined: Fri May 16, 2003 3:47 pm
Location: England
Contact:

Re: Need some more database info querying advice

Post by GedB »

the.weavster wrote:Hi GedB,

You've demonstrated how to concatenate strings in SQL but you haven't demonstrated how to marshall the contactdetails into the relevant contacttype column in a ListIconGadget. So what's the next stage, using PB to chop apart and parse subsections of the string you've just concatenated?

I'm not questioning your sql knowledge but what you've posted doesn't seem to me to offer a solution to Fangle's question.
I’m just answering the question: what is the best way to display multiple lines of contact information on a single line.

My answer is that you put it all in a single string within your sql.

I didn’t see anything about contact type columns in the text of FB’s original post. I’m guessing you’ve pulled that out of the code, which I haven’t read.

When I’ve finished work I’ll share a single query that does that. The rest is just calls from the Gadget library.

I’ll also read the code in the posts and see what they are doing.


Sent from my iPhone using Tapatalk
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: Need some more database info querying advice

Post by the.weavster »

GedB wrote:I didn’t see anything about contact type columns in the text of FB’s original post. I’m guessing you’ve pulled that out of the code
No, it was just here:
Fangbeast wrote:While processing each record, I put the title, firstname, middlename and lastname into a ListIconGadget. What's the best way of gathering multiple contact data items for each record to display on the same line as each record with this sort of linked table query?

I want to display like this:

Code: Select all

(Field names: Title Firstname Middlename Lastname    Home      Work      Fax      Mobile      Email)

                     Mr   Harry       M                Houdini        20154     020202  8710    404129873 vanish@proton.net
I read that as a separate column in the ListIconGadget for each contact type.

So "Home", "Work", "Fax", .. are contact types
and "20154", "020202", "8710", ... are their respective values
User avatar
GedB
Addict
Addict
Posts: 1312
Joined: Fri May 16, 2003 3:47 pm
Location: England
Contact:

Re: Need some more database info querying advice

Post by GedB »

If the Contact Types are fixed then a separate join for each type will do the job. Then it's just a simple 121 mapping between query and columns.

If you want it to be dynamic you can generate the SQL. It follows a clear repeating pattern.

Code: Select all

-- Create The Tables

Drop Table If Exists People;
Create Table People (
  id integer,
  firstName text, 
  lastName text
);

Drop Table If Exists Contacts;
Create Table Contacts (
  PersonId int,
  type TEXT,
  details text
);

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 Contacts (Personid, Type, Details)
Select 1, 'Home', '111 1111'
Union Select 1, 'Work', '222 222'
Union Select 1, 'Email', 'Andy@HewsonHelpline@co.uk'
Union Select 1, 'Mobile', '33333 333 3333'
Union Select 1, 'Fax', '444 4444'
Union Select 2, 'Mobile', '44444 444 4444'
Union Select 2, 'Email', 'Ben@Sidetunes.co.uk'
Union Select 3, 'Home', '555 5555'
Union Select 3, 'Email', 'Charlie@DifferenceEngine.com'
Union Select 5, 'Mobile', '66666 666 7777';

-- Execute the Query

Select 
  Person.firstname,
  Person.lastname,
  Home.details as Home,
  Work.details AS Home,
  Fax.details As Fax,
  Mobile.details As Mobile,
  Email.details as Email
From People as Person
Left Outer join Contacts As Home
On Home.PersonId = Person.Id and Home.Type = 'Home'
Left Outer join Contacts As Work
On Work.PersonId = Person.Id and Work.Type = 'Work'
Left Outer join Contacts As Fax
On Fax.PersonId = Person.Id and Fax.Type = 'Fax'
Left Outer join Contacts As Mobile
On Mobile.PersonId = Person.Id and Mobile.Type = 'Mobile'
Left Outer join Contacts As Email
On Email.PersonId = Person.Id and Email.Type = 'Email';
Post Reply