Forum Academy Marketplace Showcase Pricing Features

SQL Joins Vs Bubble

Thanks. Do you have a text version of the query ?

Here is the table script (Oracle):
CREATE TABLE sql_users
(
USer_ID VARCHAR2(4000 BYTE),
FIRST_NAME VARCHAR2(200 BYTE),
LAST_NAME VARCHAR2(200 BYTE)
);

CREATE TABLE sql_meetings
(
User_ID VARCHAR2(4000 BYTE),
Meeting_Date Varchar2(200 byte),
Meeting_Location Varchar2(4000 byte)
);

CREATE TABLE sql_tests
(
User_ID VARCHAR2(4000 BYTE),
test_Date Varchar2(200 byte),
test_pass Varchar2(10 byte)
);

Sample Data
INSERT INTO sql_users (user_ID, FIRST_NAME, LAST_NAME) VALUES (‘1’, ‘AR’, ‘Chi’);
INSERT INTO sql_users (user_ID, FIRST_NAME, LAST_NAME) VALUES (‘2’, ‘Sona’, ‘Chi’);
INSERT INTO sql_users (user_ID, FIRST_NAME, LAST_NAME) VALUES (‘3’, ‘Rom’, ‘Chi’);
INSERT INTO sql_meetings (User_ID, meeting_date, meeting_location) VALUES (‘1’, ‘05/01/2020’, ‘Japan’);
INSERT INTO sql_meetings (User_ID, meeting_date, meeting_location) VALUES (‘1’, ‘05/02/2020’, ‘USA’);
INSERT INTO sql_meetings (User_ID, meeting_date, meeting_location) VALUES (‘2’, ‘05/01/2020’, ‘Australia’);
INSERT INTO sql_meetings (User_ID, meeting_date, meeting_location) VALUES (‘2’, ‘05/03/2020’, ‘Canada’);
INSERT INTO sql_meetings (User_ID, meeting_date, meeting_location) VALUES (‘3’, ‘05/04/2020’, ‘Brazil’);
INSERT INTO sql_tests (User_ID, test_date, test_pass) VALUES (‘1’, ‘05/01/2020’, ‘Yes’);
INSERT INTO sql_tests (User_ID, test_date, test_pass) VALUES (‘1’, ‘05/01/2020’, ‘No’);
INSERT INTO sql_tests (User_ID, test_date, test_pass) VALUES (‘1’, ‘05/02/2020’, ‘Yes’);
INSERT INTO sql_tests (User_ID, test_date, test_pass) VALUES (‘1’, ‘05/03/2020’, ‘Yes’);
INSERT INTO sql_tests (User_ID, test_date, test_pass) VALUES (‘2’, ‘05/01/2020’, ‘No’);

Sample Query
SELECT UNIQUE m.meeting_date, m.meeting_location, t.test_date
FROM sql_meetings m, sql_tests t
WHERE t.test_pass = ‘No’
AND t.user_id = m.user_id

1 Like

This sounds like the sort of thread I need to be on! I am trying to do something in bubble. I failed. I asked in the forum. Someone suggested doing something and pointed out that I would have to do whatever they suggested whether I was resolving the data issue in any other software such as excel or SQL. That made me think that doing it elsewhere might clarify it in bubble so despite not knowing much about SQL I googled it and within a couple of hours I had created tables, entered sample data and solved my problem. Frustrating to say the least, seeing as I have spent weeks trying in vain to achieve it in bubble. So.… is anyone able to recreate the SQL Query below in bubble? Many thanks in advance!

SCRIPT:

CREATE TABLE defaultview
(
dvID VARCHAR2(4000 BYTE),
priority VARCHAR2(200 BYTE),
vendor VARCHAR2(200 BYTE)
);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘1’, ‘1’, ‘ebay’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘2’, ‘2’, ‘footlocker’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘3’, ‘3’, ‘nike’);
INSERT INTO defaultview (dvID, priority, vendor) VALUES (‘4’, ‘4’, ‘footasylum’);

CREATE TABLE users
(
ID VARCHAR2(4000 BYTE),
email VARCHAR2(200 BYTE),
favouritestore VARCHAR2(200 BYTE)
);

INSERT INTO users (ID, email, favouritestore) VALUES (‘1’, ‘[email protected]’, ‘ebay’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘2’, ‘[email protected]’, ‘footlocker’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘3’, ‘[email protected]’, ‘nike’);
INSERT INTO users (ID, email, favouritestore) VALUES (‘4’, ‘[email protected]’, ‘footasylum’);

CREATE TABLE allshoes
(
ID VARCHAR2(4000 BYTE),
description VARCHAR2(200 BYTE),
price VARCHAR2(200 BYTE) ,
product VARCHAR2(200 BYTE) ,
vendorid VARCHAR2(200 BYTE) ,
uniqueid VARCHAR2(200 BYTE) ,
vendor VARCHAR2(200 BYTE) ,
sole VARCHAR2(200 BYTE)
);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘1’, ‘this is the nike description air force 1’, ‘90’, ‘air force 1’, ‘JkTGzADv’, ‘aaa111’, ‘nike’, ‘no’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘2’, ‘this is the footlocker description ultrabost’, ‘169.99’, ‘ultraboost’, ‘FX1334’, ‘bbb222’, ‘footlocker’, ‘yes’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘3’, ‘this is the footlocker description air force 1’, ‘90’, ‘air force 1’, ‘23498067’, ‘aaa111’, ‘footlocker’, ‘no’);
INSERT INTO allshoes (ID, description, price, product, vendorid, uniqueid, vendor, sole) VALUES (‘4’, ‘this is the footasylum description airforce 1’, ‘90’, ‘air force 1’, ‘008939’, ‘aaa111’, ‘footasylum’, ‘no’);

QUERY:
with temptable as
(
select allshoes.product, allshoes.uniqueid, allshoes.vendor, allshoes.vendorid, Row_Number() Over (Partition By uniqueid Order By priority Desc) RN
from allshoes
left join defaultview
on allshoes.vendor=defaultview.vendor
where allshoes.uniqueid not in
(Select allshoes.uniqueid from allshoes where allshoes.sole=‘yes’ or allshoes.vendor=‘ebay’)
)

select product, uniqueid, vendor, vendorid
from temptable
where RN=1
union (Select product, uniqueid, vendor, vendorid from allshoes where allshoes.sole=‘yes’ or allshoes.vendor=‘ebay’)

Hi Nigel. This is really useful. As a developer of 30 years and a long-time SQL guy, I’m running through some scenarios with Bubble to translate that knowledge to how Bubble handles data before I embark on serious app development. One problem I’m having is summing up a field from a list of transactions with a constraint based on a field in a joined accounts table. Here’s the query I would write to get at this using a normal RDBMS.

SELECT SUM(t.postedvalue) FROM transaction t
JOIN account a ON t.accountid = a.id
WHERE a.type = false AND t.date < @paramDate

In Bubble I’ve set up the data so the Transaction data type contains a field for Account of the Account type. I’ve created a workflow to do the calculation by setting the summed value into a state value on the page. In order to set the condition to only sum for accounts of a particular type, I assumed I’d be able to just set a Constraint for both conditions of the equivalent SQL query, one for the date condition and one for the account type. However, while it allows me to select the Account in the constraint, I thought it would let me select the field in the linked Account eg Account 's Type but it will only let me set a constraint directly against the Account field/object itself.

I’ve tried everything I can think of, spent a day looking for posts, articles and videos to find an answer, tried using merge, intersect etc but can’t find an answer to this. I think I need to be very confident that I have Bubble’s DB querying paradigm nailed before I can press ahead with full development and I’m sure an answer to this will make things “click” for me.

Any help you can give me on this would be much appreciated.

Cheers, Gareth.

PS any explain on what “join with” or any other querying commands do and when I would use them would also be very useful.

1 Like

Late answer here: but you’ve compositely defined your unique_id on both meetings and test as both a PK and an FK. Hence, tests to meetings really has an inherent 1:1 relationship, since the PK num is always utilized to match them up via INNER JOIN. What you want to do in Bubble is add a field to both tables of type opposite table. (eg add to the Meetings table a field like Test_FK of type Test) and then add a type of User to BOTH as well. This will form the necessary data triangle you need to cross join everything well in Bubble. Your data insertions will look like:

Step 1 create thing (user) -> Step 2 create thing (Test) w User = step 1’s user -> Step 3 create thing (meeting) w User = step 1’s User and Test = Step 2’s test -> make changes to step 2’s test set meeting = step 3’s meeting etc. A slight mess, but this is the best way to handle a three-way FK this in Bubble.

Your INSERTS are always a series of sequential “Create thing” actions where you manually define the fields.

You sir, have a query with both a UNION and a WINDOWING FUNCTION. Ugh. The union is possible via an :plus item or :plus list action but a RANK PARTITION is a killer on performance. You’ll need to do it via a :filter.

  1. Add a datatype to your allshoes table for rank_score.

  2. Make changes to list of things (allshoes) set rank_score = 1 for list of: Select all allshoes :filtered
    Filtered is :advanced: this allshoe’s (bubble_unique_id) = [Do A search For (allshoes) where your_unique_id = This allshoe’s your_unique id SORTED BY priority desc]:item#1:bubble’s_unique_id

3.select from it via a predicate of rank_score = 1 to fetch all top-ranked groupings.

Do not expect this to scale well however.
The fact that your using unique_id as a grouping attribute is a not-so-great naming convention. It also will cause you great confusion with Bubble’s auto-identity unique_id column.

Gazinhio, you’re only short by one step. Instead of Action’s type = false, you’d literally just do

Action IS IN Do A Search For ACTION Where type = false.

This sounds horribly non-optimized, but Bubble actually refactors this somehow pretty speedily behind the scenes.

2 Likes

Would the above mentioned searching setup process work for the issues I am facing in my app mentioned in this post: Search Workflow Help

Here is a visual of the tables and how they would “inner link”. Need to add available policies in a list for search record, checking to make sure the conditions & prescriptions are “allowed” from the policy conditions and therefore add that policy to the available policies list on the search record.

Just wanted to ping @NigelG and co to say how amazing this post was, years after it was written. Bravo and thanks!

3 Likes

Thank you @akalati :slight_smile:

My old posts often help me out as well, as have forgotten so much over the years !

5 Likes

Very Helpful. Thanks For Sharing.

Thank you so much for this posting how to implement like a SQL statement in .bubble. I really appreciate it.