Raw SQL - How to approach paging and lazy fetching

[Kevin Yu]

Hi All,

I am using raw sql to connect to database. The reason we used raw sql instead of the Django model is because the database is legacy and is being shared by multiple applications...

I have one use case that I'm struggling right now. Basically I have a page that fetch more than 1000 results. My query is like this:

        cursor = connection.cursor()

        cursor.execute('''

                SELECT br.id, br.name, br.created_at, br.updated_at,

                br.branchpoint_str, br.source

                FROM branches as br

                LEFT JOIN branches_projects as bp

                ON br.id = bp.branch_id 

                WHERE bp.project_id = "%s" AND source != "other"

                ORDER BY updated_at DESC

                                   ''', [int(project_id)]

               )

Then in my template, I have this:

    {% for br in special_branches %}

      <tr class="{% if forloop.counter|divisibleby:2 %}even{% else %}odd{% endif %} highlightable" link="/files/{{build.image_path}}/build{{build.build_number}}/">

            <td class="selectable">{{br.name}}</td>

            <td class="selectable">{{br.branchpoint}}</td>

            <td class="selectable center">{{ br.source|upper }}</td>

            <td class="selectable center">{{br.updated_at}}

            <td class="selectable center">{{br.built_at}}</td>

      </tr>

    {% endfor %}

The current problem is this would create a very long page... I am wondering how to approach this problem so that I can have different page on the template, and say 100 result per page, when i click the second page, then  django will fetch result 100-200.

Thanks!

[Jani Tiainen]

Hi,

Even you do have legacy database, you can use unmanaged models and then leverage full power of ORM [1].

Just create models and in their Meta set managed = False and there you go.

And what comes to paging - you need to somehow pass offset and limit to your query.

[1] https://docs.djangoproject.com/en/1.11/howto/legacy-databases/

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f6a3df97-8218-4fb8-b200-f4535797e135%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
Jani Tiainen

[Kevin Yu]

Hi Jani,

Thanks for the link. I was aware of this link and actually followed the steps when I started the project. I tried using model but then found out when i need to query a join, it doesn't seem to work unless there's a foreign key between the two models, however, given it's a legacy database, i can't touch the schema at all. Is it possible to query join results with managed=False?

Thanks

[Jani Tiainen]

Yes, even there isn't real foreign keys in  the database you can still join models.

Have been doing that few times with legacy database. When defining fkey you need to point it to corresponding field in fkey attributes in your model if it isnt your target model pk.

And you can join nonmanged models with managed just fine.

To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7e4a44fc-e5ce-4562-8b75-09ba065e34f5%40googlegroups.com.

[Kevin Yu]

Thanks Jani. I was able to solve my original issue using your suggestion.