"distinct" based on more than 1 field

[Davy Jacops]

    db "person" with 2 fields:

    db.define_table('person',

        Field('firstname', type='string'),

        Field('familyname', type='string'),

        .....

    )

Need to make this table visible in SQLFORM.grid / SQLFORM.smartgrid, or SQLTABLE based on a JOIN query:

    q = ((db.person.id == db.othertable.person_fk))

    rows = db(q).select(db.person.firstname, db.person.familyname)

Due to the JOIN, several rows have the same person, 

but every person may be displayed only once.

So tried this:

    t = SQLTABLE(rows, distinct = True)

But still the same person is shown several times in the table.

In fact I need the combination of "firstname" + "familyname" to be distinct.

I read in the book that "distinct" can also be an expression. Can this help in this case?

And then what is the syntax?

Thank you for your help

[villas]

I think you need to use the distinct like this:

rows = db(q).select(db.person.firstname, db.person.familyname, distinct=True)

[Annet]

For distinct to work you need orderby, I don't know if that works for more than one field.

orderby=db.person.firstname | db.person.familyname, distinct = True


Kind regards,

Annet

[Davy Jacops]

Villas's solution works indeed, and I can build an SQLTABLE based on the rows now.

Thank you.  (orderby is not required)

Consequence is that SQLFORM.grid or smart grid cannot be used in this case?

(since there is no "select" statement possible, as it just takes a query?)

Op zaterdag 27 juni 2015 22:47:34 UTC+2 schreef villas:

[pa...@cancamusa.net]

El domingo, 28 de junio de 2015, 10:06:53 (UTC+2), Davy Jacops escribió:

Villas's solution works indeed, and I can build an SQLTABLE based on the rows now.

Thank you.  (orderby is not required)

Consequence is that SQLFORM.grid or smart grid cannot be used in this case?

(since there is no "select" statement possible, as it just takes a query?)

You can use SQLFORM.grid, using for example the "links" optional attribute (although there may be a more efficient way.
But first, what do you want?
If you have the following results for the join:

1.  "John Doe has a dog"
2.  "John Doe has a cat"
3.  "John Doe has a snake"

do you expect the grid to display one line?

User           Pets


John Doe       dog, cat, snake

or do you want to take the first entry and ignore the rest or what?