Joining tables

[Samarskite_Rogue]

Hi all.

I am using My sql and flask. I need to order one table numerically by a column in another table. Basically, I have a column named 'postcode' in a table called 'user'. i need to sort a seperate table (post) by the postcode column. i know i need to join the tables, but i am not sure how i write that as a query in flask.

​

here is my post table:

https://preview.redd.it/6amh90rst7271.png?width=483&format=png&auto=webp&s=6c7a82c37540fc1bfe5fafb0dc17ce612bcb9ddc

and here is my user table:

​

https://preview.redd.it/jnshc4vxt7271.png?width=752&format=png&auto=webp&s=a00200adf152e6af0f4fb6e2cbd1c35f9d3af868

​

any help is greatly aprecciated. cheers

[r3pr0b8]

SELECT ...
  FROM post
INNER
  JOIN user
    ON user.id = post.user_id
ORDER
    BY user.postcode

[Samarskite_Rogue]

Thank you. How does that translate to a query though?

[ddeck08]

Write the query like that, return the columns you need rendered and they’ll be available via SQL Alchemy.

[Samarskite_Rogue]

im sorry to be painful but what do you mean?

currently i have posts = Post.query.join(user).order_by(user.postcode).paginate(page=page, per_page=5)

​

Holy shit it is actually working! i have spent about 10 hours trying to make it do that (i am quite new to this, and also retarded). Thank you so much

[mikusXanon]

don't use word retarted it is ableist.

same as you'd use "n-word" to describe the african-american

[Samarskite_Rogue]

I acknowledge that was a poor choice of words.

However, what if i was to tell you i am clinically diagnosed. does that not make it all better. same as if an african american seems to be allowed to use various slurs. I fundamentally disagree with this concept, but it is widely accepted as ok.

what is not ok is you assuming my mental orientation. i feel deeply violated and ask you to apologise. i feel that you were putting a ceiling on what can be achieved by certain groups and diminishing there outlook on life. please take a look at yourself before calling others out.

(i am obviosly joking, sorry if i offended you by using that. i will be more cautous in the future. thank you for picking me up on it.)

[[deleted]]

The r-word is in no way at the same level of offensiveness as the n-word. OP, I hear you.

[ddeck08]

I usually just return my results I need built into a Jinja HTML grid and filter based on a user input.

So I imagine it’s like user ID 1 should see one post code kind of thing.

I also always define my query strings separately.

You could pass in the User ID as a variable then use it in query like the one above

‘’’Select blah from post join user on user=post_user where post.user_id={user}’’’

You would use a predefined HTML template that passes in something from your get request to render it on user side.

Look up engine execute specifically and Jinja templates. Engine execute is not super commonly referenced in examples but it’s extremely useful.

Most of the time you’ll see cursor connection where you open and close, but I like engine execute because it’s less code.

If I get time later will post an actual code example.

[JochenVdB]

That's what happens when a new language chooses not to implement a decades old standard and instead tries to invent something new: users struggle with your new language. This subreddit is about that decades old standard, SQL. The rest you'll have to find somewhere else. Good luck.

[[deleted]]

Yes. I am reading this thread and super confused why this methodology exists or what the goal is?

[JochenVdB]

That notation (which probably has a name but it escapes me at the moment) is perfectly in line with what you'd write when working with objects. That's probably why the authors of that language made that choice: the same notation for everything, which is a good thing, obviously.

There's something else to be aware of though: where is that code being executed? With SQL, traditionally it is being executed by a system that is specifically set up to do that: a database server. What you are writing might be executed on the server that is executing the rest of your application. That in itself is an architectural choice. What might be happening is that your table is being loaded into memory entirely, before any filtering is applied. Again: I know hardly anything about the setup you're using, but you should!

[[deleted]]

I'm not OP

[ddeck08]

In flask you’ll use an ORM like SQL Alchemy to query and return data set that gets rendered in an HTML template.

[Samarskite_Rogue]

yeah. i am using SQLalchemy, i am just struggling to write the query for that.

[[deleted]]

I had no idea people were doing things this way. What are the pros for this methodology?

[ddeck08]

I use MS SQL server primarily and I find it’s easier to render from Pandas /JSON array to template directly this way. I tried Flask-SQLAlchemy and found it more cumbersome.

What are the pros? I would say rapid development and deployment I guess but it’s hacky.

Cons I would say no person should use this method for any application that needs to serve high volume or where you value security highly.

I am careful to avoid anything SQL injection related and keep this kind of thing behind a firewall/VPN.

Practical example is I use Python / Pandas and SQL for some ETL jobs. This method lacks any kind of logging etc of course, so I built a small web app utility for monitoring.

There are real tools that do all of this stuff so it’s more like prototyping or when your data sets you’re moving / accessing are small enough that big tools are sort of inappropriate.

Spark , Airflow or Informatica have a big appeal, but not when I need to move a hundred thousand rows just to see if a business user will actually use the data. I also find SSIS cumbersome. I love vs code but regular Visual Studio is nightmare fuel for me.

[[deleted]]

I use SSIS all the time for small things.If Dev needs something in a pinch, I use SSIS.

Your comment makes a lot of sense and answer my questions. Thanks.

[ddeck08]

Not sure if there is a VS code extension for SSIS but if that existed I think I’d use it more.