mysql - Selecting many items, which themselves have many items, in a single query? -

-

i have database representing book collection. looks this:

books table

id    |    title            |    year    |    author_id -------------------------------------------------------- 1       how read              1985            7 2       dream of spring        2041            14 3       tour guide               2010            40

tags table

id    |    tagname ------------------- 1        nonfiction 2        fiction 3        poetry 4        sci-fi 5        magical realism 6        detective

books_tags_links table

book    |    tag ------------------ 8            4 10           1 10           4 10           5 8            2

there one-to-many relationship between books , tags: book can have multiple tags, 'fiction, detective, sci-fi'.

what i'd know is: possible me select books, and tags associated each book, in single query? don't know result -- jagged array, suppose, each book having different number of tags?

my ultimate aim output html table each row book, tr element having data-tag property each tag book's been given, can use javascript hide or show books combination of tags. far can imagine how issuing separate select tags query every book in list, mean making thousands of database requests, , want know if there's smarter way it.

i using sqlite @ moment, if there's solution exclusive postgres or mysql i'd still interested in learning it.

in postgres should use array_agg() function aggregate tags linked book.

select title, array_agg(tagname) books b join books_tags_links l on l.book = b.id join tags t on l.tag = t.id group 1

in sqlite3 have similar function group_concat() (array_agg() returns array, group_concat() - string).


Comments

Post a Comment

Popular posts from this blog

python - TypeError: start must be a integer -

-
i trying draw 5 turtles, getting typeerror on line 25. here code: import turtle wn = turtle.screen() redrose = turtle.turtle() color = input("what background color be?") fillcolor_f = input("what color of rose be?") redrose.hideturtle() redrose.speed(30) redrose.penup() redrose.left(180) redrose.forward(175) redrose.right(90) redrose.forward(30) redrose.right(90) redrose.pendown() def drawrose(red): redrose.color("pink") redrose.fillcolor(fillcolor_f) redrose.fill(true) in range(red): redrose.forward(i) redrose.right(49) in range(5): drawrose(redrose) redrose.penup() redrose.forward(350) redrose.right(144) redrose.pendown() redrose.fill(false) drawrose(50) wn.bgcolor(color) i trying draw 5 roses, produces errors. doing in interactivepython.org. you recursively calling drawrose wrong parameter. on line 23 ( for in range(red): ) expect red integer...
Read more

c# - DevExpress RepositoryItemComboBox BackColor property ignored -

-
i have following code almost works properly; private void cbstatus_drawitem(object sender, listboxdrawitemeventargs e) { string item = e.item string; if (item != null) { switch (item) { case "1": e.appearance.forecolor = color.green; e.appearance.backcolor = color.green; break; case "2": e.appearance.forecolor = color.orange; e.appearance.backcolor = color.orange; break; case "3": e.appearance.forecolor = color.red; e.appearance.backcolor = color.red; break; } } } when dropdown shown, items forecolor correct, backcolor remains whatever backcolor of theme is; i.e. if i've got set dark theme, backcolor dark, cells in gridview, rather being green/orange/red. i've tried setting e.appearance.options.usebackcolor trying set e....
Read more

django - Creating multiple model instances in DRF3 -

-
1) create multiple model instances 1 api call, asked here: how create multiple model instances django rest framework? tried solution named in link, no success. i trying upload multiple files in 1 api call. result: files uploaded (only once overwrote perform_create) 1 instance created (if send 2 files, latter created instance). my code: class fileuploadserializer(serializers.modelserializer): def __init__(self, *args, **kwargs): many = kwargs.pop('many', true) super(fileuploadserializer, self).__init__(many=many, *args, **kwargs) class meta: model = fileupload read_only_fields = ('created', 'datafile', 'owner') class fileuploadviewset(viewsets.modelviewset): queryset = fileupload.objects.all() serializer_class = fileuploadserializer parser_classes = (multipartparser, formparser, ) def perform_create(self, serializer): file_list = self.request.data.getlist('file') ...
Read more