It is currently Tue Sep 17, 2019 7:40 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: Need some more database info querying advice
PostPosted: Thu Jun 27, 2019 6:03 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4492
Location: Not Sydney!!! (Bad water, no goats)
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
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:
(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:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 9:01 am 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
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:
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:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 9:29 am 
Offline
Addict
Addict
User avatar

Joined: Fri May 16, 2003 3:47 pm
Posts: 1310
Location: England
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:
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.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  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.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?


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 10:14 am 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 12:11 pm 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4492
Location: Not Sydney!!! (Bad water, no goats)
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 12:59 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 16, 2003 3:47 pm
Posts: 1310
Location: England
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 1:58 pm 
Offline
Addict
Addict
User avatar

Joined: Thu Jul 03, 2003 6:53 pm
Posts: 1278
Location: England
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:
(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


Top
 Profile  
Reply with quote  
 Post subject: Re: Need some more database info querying advice
PostPosted: Fri Jun 28, 2019 2:47 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 16, 2003 3:47 pm
Posts: 1310
Location: England
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:
-- 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';


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye