Complex Query Conundrum

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Complex Query Conundrum

Post by customaware »

Always seem to struggle with some of these types of queries...

We have 3 BOs... Customer, Agency, Store

A Customer can be related to many Agency .... So, Customer has a Peer Many reference to Agency
A Store can be related to many Agency .... So, Store has a Peer Many reference to Agency

I know the Customer and I want a Query to display all of the Stores that are in an Agency that is related to that Customer.

I expected one of these to work... but no cigar...

FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThisStore.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThatStore.pm_Agency)>0

Any ideas or suggestions would be appreciated.
Last edited by customaware on Wed Oct 20, 2021 10:29 am, edited 1 time in total.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Complex Query Conundrum

Post by PointsWell »

Code: Select all

Agency IN Agency IN 
Is this a typo in your post or in your quey?

Agency cannot be in Agency
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Complex Query Conundrum

Post by customaware »

It’s a typo in my post.

I’ll edit it.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Complex Query Conundrum

Post by PointsWell »

I tend to avoid IN and do the reverse relationship but it's not clear what the Agency.p_Store relationship.

If it's a ps then I would do

Code: Select all

FIND Store WHERE COUNT Agency WHERE ( Agency.psCustomer = Customer AND Agency.psStore = Store)>0
If they are many to many then I avoid using these in AIM and instead create an intermediary BO that handles the 1-m-1 relationship.

So CustomerAgent BO and StoreAgent BO

In part because that is what is happening anyway and in part because with lists you have little auditability - you can't track the date that a relationship became active and when it becomes inactive (without doing an audit log table)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Complex Query Conundrum

Post by customaware »

I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Complex Query Conundrum

Post by PointsWell »

eagles9999 wrote: Wed Oct 20, 2021 11:13 am I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers
I'm outta ideas then beyond the intermediary BO CustomerAgent.

Agent.pmCustomerAgent
CustomerAgent.psAgent
CustomerAgent.psCustomer
Customer.pmCustomerAgent
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Complex Query Conundrum

Post by BLOMASKY »

I don't suppose you want to use a Stored Procedure to retrieve the records?

Bruce
himanshu
Posts: 722
Joined: Thu Jun 19, 2008 6:24 am
Location: India
Contact:

Re: Complex Query Conundrum

Post by himanshu »

:idea:
FIND Store WHERE (EXISTS Agency WHERE (Store IN Agency.pm_Stores AND Agency IN Customer.pm_Agency))
From,
Himanshu Jain


AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Complex Query Conundrum

Post by customaware »

Thankyou all for your suggestions.....

This has turned out to be one of those situations where the actual data can trip you up.

In my original try....
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0

It seemed incorrect because I was getting back ALL Stores which is not the answer I was expecting.

After frigging around with this for 2 days, I have now realised that one of the Agencies actually does have ALL Stores assigned to it. So the result was
in fact correct.....

When I limited the query to a particular Agency like this...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency AND Agency.ID=17)>0
Then it returns the correct number of Stores.

So, problem sorted.

Apologies for wasting everybody's time :-/
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Post Reply