Django query from database

[Kamal Kaur]

Hello there,

Hope you are doing well :)

I have a problem regarding querying a list from two tables, the
procedure goes like:

Considering two tables from mysql database:

1. UserProfile table, with complete client details: First name, Last
name, Address, email id, Contact number etc.
2. CodeTable, with only two columns 'Word' and 'Code'. Codes are
soundex codes for the words in first column like:

Word Code
David D130
Henry H560
Ostro O236
Milton M435
Henroo H560

These 'words' are also contained in UserProfile table somewhere, can
be in any column. I have a list A with soundex codes, entered by user,
obtained with request.GET like:
A= ['D130', 'H560', 'O236']

Here starts the problem, I have to fetch 'Words' from CodeTable
corresponding to the 'Codes' in list A, meaning that I need another
list B, like:

B = ['David', 'Henry', 'Ostro', ' Henroo']

Because the code H560 is same for ' Henroo', it will also be there in
list B as we are searching for all the words that have 'codes' in list
A.

For this list B, I want to search all the client details i.e. I want
to display all the clients that have any of the words in list B from
UserProfile table.

I am not getting how to accomplish this searching using Django
queries. I am not able to make a view/function for this logic. Please
help and feel free to ask details if problm is not clear.

Thanks ^_^


--
Kamaljeet Kaur

[Leonardo Giordani]

I think that you have to perform a query for each Code, extracting the list of Word matching that Code and then merging all the lists.

Since I don't know your models, I'll give you some general code, let me know if you understand how to implement it exactly on your models.

Assuming that the CodeTable is the representation of a Word model:

B = []

for code in A:

    words = Word.objects.filter(code=code)

    B.extend(words)

This is the basic algorithm. You can then make all sorts of Python magic tricks to shorten it or make it faster, but if you are not dealing with billions of data I do suggest you to keep it simple and readable.

If you need to have each element in B appear just one time (uniqueness) just perform a B = set(B) at the end.

Let me know if it works. =)

Cheers
Leo

Leonardo Giordani

Author of The Digital Cat
My profile on About.me - My GitHub page - My Coderwall profile

2013/10/11 Kamal Kaur <kamal....@gmail.com>

--
Kamaljeet Kaur

--
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 http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAP8Q%2BxhO%3DeZJjdui4OOw1T3V2QqtJW_PnC-BTTCDdBogipc8sw%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Kamal Kaur]

On Fri, Oct 11, 2013 at 4:06 PM, Leonardo Giordani
<giordani...@gmail.com> wrote:
> I think that you have to perform a query for each Code, extracting the list
> of Word matching that Code and then merging all the lists.

Exactly!

> Since I don't know your models, I'll give you some general code, let me know
> if you understand how to implement it exactly on your models.

Here are these two models: http://tny.cz/82158e83

<snip>

> This is the basic algorithm. You can then make all sorts of Python magic
> tricks to shorten it or make it faster, but if you are not dealing with
> billions of data I do suggest you to keep it simple and readable.

Ok.

> If you need to have each element in B appear just one time (uniqueness) just
> perform a B = set(B) at the end.

Ok.

> Let me know if it works. =)

Yes it will work! But after extending list B, I want to search the
elements of list B from UserProfile table. Here the word can be
anywhere and can be more than one times. Also two words in list B can
be in same column. Here also the results must be unique.

Also how to get the output in a template? The tuples containing
elements of list B from UserProfile table are to be displayed in that
template.

[Leonardo Giordani]

Sorry, I forgot the User part of your question.

Let me understand the exact relationship between codes, words and users: do those words or codes come from a form? Or are saved in the DB for each user?

I ask this because I want to understand if you are trying to get a relationship that already exists on the DB (so it is a problem of doing good queries and building smart Python structures) or if you are trying to manage some new data the user enters in your site.

If you find the time to better describe the exact flow of your application I hope I can help you find a good solution for your problem.

Cheers

Leo

Leonardo Giordani

Author of The Digital Cat
My profile on About.me - My GitHub page - My Coderwall profile

2013/10/11 Kamal Kaur <kamal....@gmail.com>

On Fri, Oct 11, 2013 at 4:06 PM, Leonardo Giordani

--
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 http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAP8Q%2BxidZD%2B6mo2z4POabcX_WBFPvEk%3D%3Dg%2BOPNET6u0SYmM%3DkQ%40mail.gmail.com.

[Kamal Kaur]

On Fri, Oct 11, 2013 at 6:43 PM, Leonardo Giordani
<giordani...@gmail.com> wrote:
> Sorry, I forgot the User part of your question.
>
> Let me understand the exact relationship between codes, words and users: do
> those words or codes come from a form? Or are saved in the DB for each user?

Actually I have to search for client details (from UserProfile table)
ignoring vowels. Because new users come from time to time. During
their registration, the phonetic codes of all the words entered are
saved (in CodeTable) along with the actual word.

When a string is searched for, in search box, it is split into words
and then these words are converted to phonetic codes which are to be
searched in CodeTable. The words corresponding to matching codes will
be searched for in UserProfile table which will be given as output for
searched keywords i.e No matter if a user enters "Smyth" or "Smith",
output will be on the basis of their sound. And thats all :)

<snip>

> If you find the time to better describe the exact flow of your application I
> hope I can help you find a good solution for your problem.

Here you go :)

--
Kamaljeet Kaur

[Leonardo Giordani]

Ok, I think I got the point.

So, correct me if I didn't get it right, you have to get users which have first_name, last_name, or other fields equal to the words in your list B.

This can be accomplished by looking at each field for each keyword. I would also compile a dictionary keeping reference of what field matches the search
Continuing the code I wrote above:

users = {}

for field in ['first_name', 'last_name']:

    # build something like {'first_name__in', ['David', 'Henry', 'Ostro', ' Henroo']}

    filter_dict = {field + '__in': B}

    # find all users that match

    results = User.objects.filter(**filter_dict)

    # store each user and the list of its fields that match the search

    for user in results:

        try:

           users[user].append(field)

        except KeyError:

            users[user] = [field]

This is perhaps the worst Python code I ever wrote, but I hope you can tolerate it. Also beware that it is written directly in the mail composer (so indentation is wrong and code is untested).

Now I realize that the "__in" trick can be used also for the first code I wrote, try it.

As for the template, you can pass the users dict in the context. In the template loop over the key/values with

{% for user,fields in users.iteritems %}

{% endfor %}

inside this loop you can further loop over fields with

{% for field in fields %}

{% endfor %}

I am not sure if inside this last loop you can write something like

{{ user.field }}

since I have to check if the template attribute retrieving syntax also performs getattr. if this does not work you have to store tuples as values of the users dict, instead of fields, each tuple being (field, getattr(user, field)).

Try to give a sense to all this stuff and let me know!

Cheers,

Leo

Leonardo Giordani

Author of The Digital Cat
My profile on About.me - My GitHub page - My Coderwall profile

2013/10/11 Kamal Kaur <kamal....@gmail.com>

On Fri, Oct 11, 2013 at 6:43 PM, Leonardo Giordani

--
Kamaljeet Kaur

--
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 http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAP8Q%2Bxi-%2BPibMh_DsMyf14UUpZ_BkSEw5nWTRkdYyzoCDkpJWA%40mail.gmail.com.