-
Notifications
You must be signed in to change notification settings - Fork 66
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Backreferences in defining colums #83
Comments
Can you also post your query? It should be something like this
Then you can add columns like so:
Of course this will result in a table row for each combination of Parent<->Child you have. If you want to have one row for every Parent, then you have to aggregate the Children into one string, e.g. a comma separated list. You should be able to do that with
And a column like:
|
dear tdamsma thank you very much for your quick answer.
and the columns:
In this case everything works well
with the relevant column:
This error message appears:
As far as I can understand, something happens when the group_by clause is added to the query. p.s. In one of my tries I forgot to append .group_by(Parent.id) to the query. I that case the error message was:
I don't know if this is relevant here or is a different error. |
@pankus, glad to help. Forgot about that, but indeed Luckily, using SQLAlchemy this is not too difficult. From the top of my head (so might contain some mistakes): parent_with_children = dbsession.query(
Parent.id,
func.string_agg(Child.name, ', ').label('children')
).\
outerjoin(Child).\
group_by(Parent.id).\
cte()
query = db.session.query().select_from(parent_with_children )
columns = [
ColumnDT(parent_with_children .c.id, search_method='yadcf_range_number_slider'),
ColumnDT(parent_with_children .c.children, search_method='yadcf_text'),
] Note that if your data set is large, this might be a bit slow, as I believe the join between parent and children is always run over the entire parent and children table. The result of this join is put in a CTE, which is then filtered and paginated. Also, this CTE is not indexed. To overcome this, you could define a separate Materialized view for the parent_with_children table, which you could index/limit/filter as any other table, with no performance impact. Of course, you then need to put in triggers or other logic to keep the Materialized view up to date. Another alternative would be to partially denormalize you schema, by storing the children in the parent in e.g. a JSONB column. Of course this has other implications |
Congratulations @tdamsma. I'm really impressed by your knowledge of this subject. I'm new to sqlalchemy and above all I'm not a real programmer, thus all this appears to me really as a true alchemy. Just the last question (only to learn something more). What about the error message Did you understand what does it means? Does it depend on .group_by() clause? |
In the function |
First, thank you so much for your work. It is great!
I'm trying to manage a table having m:m relationship mapped with a backref. Something similar to the Parent:Child scheme as described in the sqlalchemy tutorial (http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many).
By defining the columns for the Parent class, for instance, if I use the backreference Parent.children this message appears: " DataTables warning: table id=maindata - Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Parent.children has an attribute 'cast' ".
In sum, this code does not work as I expect (but I'm a newbie):
Is there any workaround to solve such a case?
The text was updated successfully, but these errors were encountered: