How to do Subquery inside FROM?

Using Django Using the ORM
1

I need to get just one column that will have all values from another columns list to do annotations on it.

I want to do in QueryManager something like:

self.annotate(myvalues=Subquery(self.values(*["field1", "field2"]), output_field=CharField()))

But I get error
{ProgrammingError}ProgrammingError(‘subquery must return only one column\nLINE 1: …".“field3”, (SELECT U0…\n ^\n’)

When I check Subquery(self.values(*["field1", "field2"]).query, I don’t have field3 in it. But I indeed have more then one field on output (field1 and field2).

I’ve seen also this thread https://stackoverflow.com/a/19650285 that says that I need to do SELECT inside the FROM. But I haven’t a way to do it in the Django ORM.

Is there pretty way how to do that I need?

2

I’m sorry, I’m not following what you’re asking for here.

It’s likely going to be easier if you show a minimal subset of the actual models you’re working with along with what you’re expecting to see as the result.

3

For example

class Partner(Model):
    ...
    facebook = CharField(max_length=128, blank=True, null=True, default=None)
    email = EmailField(blank=True, null=True, default=None)

And I want to get facebook and email of a few (or all) partners as separate rows inside one column. The column can be named any way.

So I need something like:

contacts_values
---
myfacebook
myemail
another partner facebook
another partner email
4

Let’s dig a little deeper here - I’m starting to get the impression that this could possibly be an XY problem.

Once you’ve retrieved this data, what are you doing with it in your view? Are you rendering a template? Returning it as JSON data? It may not be necessary to do what you’re looking to do in order to achieve your real objective.

In these cases, you want to work with ORM and the various possible outputs as a unified system, not necessarily as separate and distinct steps. Understanding both models involved - and the relationships between them, along with knowing the desired results is important.

5

I will do another complex logic inside database (would be good in ORM). The output of this QueryManeger will be used in a lot of another places of app. So it is something like core logic and that I want to do on database level (not python).

6

Again, a complete definition of the models with the actual output needed and how it would be used would be helpful here.

Specifically in this case, from the limited information you’ve provided so far, I don’t see any reason why a reference to related objects isn’t a suitable solution. The creation of those object is done from the results of the query - the work is being done at the database level.

7

Honestly I a bit not understand what exactly you want from me and why is it so important to resolve the initial issue. Is the requirement that all values from a few fields should be inside intermediate column not enough? It’s just the start for me. As I said there will be a long chain of annotates and another actions on this column. But important here to have 1 column with all data and no more columns (like individual for facebook and email) because the next logic will be too complicated and duplicated due to that.

What do you mean under the “reference to related objects”? Can you give me example?

8

Because your request is ambiguous. I can come up with at least 2 and probably 3 different interpretations of what you might be looking for, and what you’re going to be doing with it after you’ve finished the query. How you’re going to access and use that data can affect how you want to “shape” it during the retrieval.

The objective is to help you do things correctly within Django. If you’re going to be effective using the framework, then you need to understand how to properly work with it and not just try to implement “something” because it’s the first solution you thought of.

I will gladly do so, if you post the two models involved in this issue, and a sample of what it is that you’re doing with this data after retrieval.

9

Ok, I am really not sure that it will help, specifically for another users, because we start to resolve a bit another task and the initial question about a subquery in the FROM is not answered. Do I understand correct that I can’t do it straight ways?

So, suppose if to simplify the task, I have one more model like:

class Product(Model):
    name = CharField()

I hope you will not ask me why I need it, because I will have to tell a lot about the app itself, but just believe me, please, that I need to return contacts list which are not substrings of products names. The connection to exact partners is not important for now, so suppose that we try to get contacts data for all partners.

Intermediary I will need to make a few transformations of contacts fields too (I planned to do it via annotate).

10

Again, your terminology is ambiguous.

As worded, I cannot answer your question - not necessarily because it can’t be done, but because I have not yet seen a specification of the desired output to know what it is you’re looking to get.

I don’t need to know the “why”, but I do need to know the “what”.

That’s why I generally request such information using the actual data - doing so tends to make it clear what is being requested.

11

Additionally to a part that I sent

I can add Product.name that can contain fields like “product1”, “myemail”. So, on the output of the QueryManager that we write should be rows:
“myfacebook”, “another partner facebook”, “another partner email”

Was that what you expect?

12

The Django ORM is not just a wrapper around a query. It is a full Object-Relational Mapper.

This means that to use the ORM as it is intended, you need to think about its usage as retrieving Python objects from the database, and not just as a way to write queries.

So when I talk about needing to understand what you are looking for to be returned, I’m looking for the structure of the Python objects that the queries will return. (This is sometimes colloquially referred to as the “shape” of the data.)

The ORM is incredibly powerful, and with the add-ons that can be included, can do just about anything that you can do with native SQL - and still providing the ability to use SQL when it is necessary to do so.

But - the final objective is always a set of objects, not a set of rows.

That’s why I keep asking about the output. I’m trying to understand the structure of the Python objects you will be creating.

And, in any non-trivial situation, it’s always easier to talk about the real data fields being referenced. When you show the actual models involved and the desired model instances being created, there’s a lot less chance of confusion when creating the transformation from those models to those objects.

13

I want at the output something similar like values/values_list returns. There will be just one column with data like:

contacts_values
---
myfacebook
another partner facebook
another partner email

I would like to have it as a QuerySet that I will able to transform on a database level more. But generally I will convert it to a python list probably.