Plzz?

« first day next day → last day (40 days later) »

Stefano Palazzo

16:04

a quick question @marco

Marco Ceppi

Sure

Stefano Palazzo

I'm storing JSON in one of the fields of my SQL table, because I don't know what fields I will need in the future. Is that okay? Do people do that?

Marco Ceppi

Will you ever need to search by JSON or is it just "extra" information?

Stefano Palazzo

there's a good chance I will want to search it eventually

Marco Ceppi

that's not best then

What you'll want to do is build a key pair table relationship somewhere

So if you have

+-------------------------------+
| Foo                           |
+-------------------------------+
| ID                            |
| Name                          |
| Bar                           |
+-------------------------------+

+-------------------------------+
| Foobars                       |
+-------------------------------+
| Foo_ID                        |
| Key                           |
| Value                         |
+-------------------------------+

yes, if you have that.

Stefano Palazzo

16:10

I see! Does SQL have any special tools for this kind of thing, or would I just have multiple selects?

Marco Ceppi

Nothing really special. Just use a join

Stefano Palazzo

that's excellent, thanks

Marco Ceppi

SELECT F.*, FB.Key, FB.Value FROM Foo AS F, Foobars AS FB WHERE FB.Foo_ID = F.ID AND (Rest of your criteria)

MySQL will create an Inner Join for you and return all the rows from Foobars

Stefano Palazzo

that's just what I was looking for

Marco Ceppi

I think that's an inner join. More over I think you need an Inner join. Maybe it's an Outer Join. let me go test really quickly.

I always get confused with inner/outer joins

Stefano Palazzo

16:14

I'm using SQLite, so I'm not positive that everything will work

it's the old story. apart from sqlachemy, there's no SQL module for python 3000. And SQLAlchemy is just a bit daunting

Marco Ceppi

Well inner/outer joins are a part of the standard SQL construct

that exact syntax might not work. It's kind of (iirc) MySQL short hand for creating a join

Stefano Palazzo

that shouldn't be a problem, I'm still learning SQL itself; I'll figure out the differences

Marco Ceppi

Yeah, the above syntax will work in MySQL, this is the expanded "SQL" syntax

-- "Short" hand join
SELECT F.*, Fb.opt, Fb.val
FROM `marco_plzz`.`foo` AS F, `marco_plzz`.`foo_bars` AS Fb
WHERE F.id = Fb.foo_id
AND F.id = 1;
-- Expanded
SELECT F.*, Fb.opt, Fb.val
FROM `marco_plzz`.`foo` AS F
INNER JOIN `marco_plzz`.`foo_bars` AS Fb ON F.id = Fb.foo_id
WHERE F.id = 1;

you could also do items from the second table, like WHERE Fb.opt = 'keyname' to get all the user's with that same key and the key's value, etc, etc

Have fun! :D

Stefano Palazzo

hehe :-)

I'll try. Thanks again!

« first day next day → last day (40 days later) »

all rooms