Using fields parameter or method without flatten=True does not seem to work on values and values_list

[pawamoy]

Describe the bug
I'm trying to get a single field from a filtered set of rows.

I've tried the following:

branches = await ProjectDependency.objects.filter(project__id=project_id).values_list(fields=["git_branch"])
branches = await ProjectDependency.objects.filter(project__id=project_id).fields("git_branch").values_list()

But they both return other columns as well as git_branch:

[
  [
    "develop",
    1,
    "project name A",
    "project code A",
    1,
    "platform A"
  ],
  [
    "",
    1,
    "project name B",
    "project code B",
    1,
    "platform B"
  ]
]

If I add flatten=True, it works correctly:

branches = await ProjectDependency.objects.filter(project__id=project_id).values_list(fields=["git_branch"], flatten=True)

[
  "",
  "develop"
]

Is this intentional? Maybe I'm missing something.
I would expect the version without flatter to return this:

[
  [""],
  ["develop"]
]

Versions (please complete the following information):

• Database backend used: sqlite
• Python version: 3.8
• ormar version: 0.11.2
• pydantic version: 1.9.1
• if applicable fastapi version: 0.79.0

[collerek]

Can you please provide models so I can reproduce?

[pawamoy]

Sure, that would be something like this (stripped down version):

import databases
import ormar
import sqlalchemy


class BaseMeta(ormar.ModelMeta):
    database = databases.Database("sqlite:///db.sqlite")
    metadata = sqlalchemy.MetaData()


class Project(ormar.Model):
    class Meta(BaseMeta):
        tablename = "projects"
        constraints = [ormar.UniqueColumns("name")]

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=100)


class ProjectDependency(ormar.Model):
    class Meta(BaseMeta):
        tablename = "projects_dependencies"
        constraints = [ormar.UniqueColumns("project", "git_branch")]

    id: int = ormar.Integer(primary_key=True)
    project: Project = ormar.ForeignKey(Project, related_name="dependencies", ondelete="CASCADE")
    git_branch: str = ormar.String(max_length=100)

[collerek]

What happens is that when you query by the id of the related model (project__id=project_id) so the Project model is added automatically to select_related and since you do not provide exclude for this model it follows the default behavior and all fields are included.

So to make it work as you expect you need to explicitly exclude the project from the fields.

project_id = (await Project.objects.get()).pk
branches = await ProjectDependency.objects.filter(
    project__id=project_id).fields(["git_branch"]).exclude_fields("project").values_list()
assert branches == [('Branch A',), ('Branch B',)]

[pawamoy]

Ah, OK, thanks. It feels weird to have to both include and exclude fields though.
Also, maybe it would work better if I don't join on projects:

branches = await ProjectDependency.objects.filter(project=project_id).fields("git_branch").values_list()

[collerek]

Yep if you won't join you don't need to exclude the related.

I will leave this issue open as when you provide fields for the main model and do not include the related one it should already be excluded, so that is indeed a bug.

[pawamoy]

Alright, sounds good to me 🙂
Thanks a lot for your help @collerek