On Oct 11, 11:36 am, cult hero <[email protected]> wrote: > This is actually kind of a continuation of a previous thread, but that > one is solved. > > http://groups.google.com/group/sequel-talk/browse_thread/thread/f683c... > > Using eager_graph plus the set_graph_aliases method lets me pull a > nice subset of data with associations in one query that works > perfectly. All the models still work as expected, etc. I added another > association in there (also a many to one) that linked to an article's > source (a magazine issue in most cases). Again, it worked perfectly. > > Trouble began when I added authors into the mix which is a > many_to_many association since some articles have multiple authors. > This doesn't seem like a problem that can be handled in a single query > (I could be wrong, but joins basically create virtual tables which > does not lend itself to mixing in a many to many in that query). > > So, this is the chain of methods I use to get my list: > > Articles.order(:slug). > eager_graph(lang). > eager_graph(:source). > set_graph_aliases( > :id => [:articles, :id], > :slug => [:articles, :slug], > :title => [lang, :title], > :summary => [lang, :summary], > :source_slug => [:source, :slug], > :source_title => [:source, :title] > ).all > > To grab all the authors in one query I replaced .all with: > > .eager(:authors).all > > And, basically, it seemed to work like I would have wanted. Two > queries and everything gets loaded. That :authors association looks > like this: > > many_to_many :authors, > :class => :ArticleAuthor, > :left_key => :article_id, > :right_key => :author_id, > :join_table => :articles_authors_join > > If I pull up a page that lists 5 articles, I get a query like this: > > SELECT "article_authors".*, "articles_authors_join"."article_id" AS > "x_foreign_key_x" FROM "article_authors" INNER JOIN > "articles_authors_join" ON (("articles_authors_join"."author_id" = > "article_authors"."id") AND ("articles_authors_join"."article_id" IN > (637, 591, 462, 55, 688))) > > Not a problem. However, if I grab a page with many more articles, I > get queries that look like this: > > SELECT "article_authors".*, "articles_authors_join"."article_id" AS > "x_foreign_key_x" FROM "article_authors" INNER JOIN > "articles_authors_join" ON (("articles_authors_join"."author_id" = > "article_authors"."id") AND ("articles_authors_join"."article_id" IN > (637, 591, 462, 55, 688, 693, 705, 14, 463, 662, 114, 467, 263, 522, > 24, 518, 595, 173, 240, 632, 153, 469, 493, 509, 500, 502, 115, 626, > 601, 47, 480, 7, 8, 489, 543, 594, 31, 630, 631, 65, 75, 560, 692, > 438, 486, 491, 521, 503, 675, 123, 29, 56, 520, 45, 217, 615, 372, > 453, 451, 447, 513, 514, 616, 618, 619, 515, 404, 410, 414, 508, 704, > 567, 703, 538, 195, 405, 137, 634, 685, 205, 204, 202, 201, 196, 578, > 1, 681, 627, 478, 298, 690, 472, 209, 200, 22, 12, 28, 608, 4, 21, > 464, 58, 663, 362, 497, 454, 439, 182, 183, 181, 572, 458, 396, 250, > 643, 403, 241, 116, 291, 292, 603, 695, 145, 147, 426, 391, 564, 569, > 565, 573, 459, 415, 620, 511, 33, 428, 542, 41, 49, 244, 470, 628, 23, > 600, 186, 172, 171, 170, 294, 267, 160, 281, 286, 287, 268, 265, 283, > 284, 161, 285, 38, 606, 238, 237, 236, 316, 337, 317, 318, 319, 320, > 321, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, > 336, 338, 339, 239, 340, 341, 342, 343, 345, 346, 347, 348, 349, 350, > 351, 353, 354, 355, 356, 168, 117, 697, 682, 457, 607, 30, 609, 26, > 455, 119, 57, 37, 653, 408, 411, 194, 534, 461, 39, 651, 561, 541, > 553, 483, 536, 302, 178, 549, 125, 366, 563, 258, 579, 580, 169, 419, > 420, 658, 566, 79, 128, 650, 425, 197, 253, 142, 304, 684, 435, 295, > 533, 612, 86, 102, 88, 95, 406, 412, 645, 687, 654, 468, 465, 216, > 309, 382, 77, 311, 251, 121, 140, 144, 146, 152, 235, 80, 535, 532, > 445, 93, 127, 269, 537, 313, 100, 429, 305, 635, 108, 660, 666, 104, > 97, 103, 597, 701, 87, 107, 206, 584, 118, 421, 577, 98, 545, 199, 82, > 139, 374, 375, 562, 99, 586, 364, 365, 370, 373, 376, 446, 307, 245, > 400, 84, 649, 89, 255, 254, 528, 557, 252, 90, 81, 495, 402, 94, 151, > 656, 92, 674, 571, 574, 203, 531, 529, 501, 393, 96, 296, 524, 527, > 397, 424, 76, 444, 440, 300, 661, 613, 109, 85, 106, 165, 702, 519, > 226, 390, 141, 371, 78, 110, 166, 523, 481, 314, 310, 315, 83, 399, > 368, 162, 707, 143, 91, 582, 686, 52, 34, 36, 62, 494, 617, 213, 51, > 437, 149, 401, 409, 135, 297, 63, 17, 544, 546, 525, 596, 13, 5, 540, > 312, 60, 156, 694, 301, 282, 646, 218, 193, 207, 35, 539, 479, 398, > 184, 668, 669, 670, 673, 485, 46, 155, 20, 434, 698, 352, 691, 322, > 585, 593, 308, 505, 136, 154, 556, 167, 504, 132, 192, 450, 72, 71, > 659, 70, 68, 303, 67, 69, 73, 66, 306, 709, 357, 219, 359, 220, 360, > 361, 379, 380, 381, 383, 384, 385, 386, 387, 221, 388, 210, 10, 407, > 32, 344, 449, 148, 640, 101, 621, 214, 499, 548, 592, 678, 185, 583, > 547, 442, 11, 293, 120, 443, 623, 611, 138, 602, 9, 587, 590, 676, > 261, 27, 74, 416, 507, 159, 6, 498, 367, 581, 377, 215, 605, 473, 43, > 157, 131, 436, 696, 112, 259, 477, 189, 211, 506, 133, 130, 134, 208, > 648, 700, 187, 394, 61, 180, 369, 242, 568, 198, 488, 105, 644, 129, > 657, 177, 604, 433, 278, 664, 555, 158, 647, 476, 273, 25, 456, 228, > 231, 229, 230, 234, 227, 232, 224, 233, 225, 223, 622, 176, 706, 18, > 363, 260, 510, 636, 638, 639, 641, 598, 222, 679, 427, 389, 243, 699, > 418, 40, 175, 448, 625, 559, 624, 570, 44, 212, 42, 667, 3, 174, 378, > 677, 430, 708, 614, 552, 672, 633, 642, 665, 599, 466, 710, 262, 16, > 417, 2, 54, 558, 64, 655, 15, 680, 432, 48, 59, 126, 257, 190, 50, > 422, 652, 554, 551, 460, 191, 392, 576, 588, 288, 289, 290, 274, 275, > 276, 277, 256, 272, 279, 280, 264, 266, 270, 271, 413, 113, 53, 19, > 179, 423))) > > This doesn't seem like it's gonna scale well. Is there a better way to > do this? I can always do my own query to grab the authors as separate > dataset and go from there, but that doesn't seem like the most elegant > solution either. > > (Realistically, the lists will be paginated so this query isn't likely > to occur in reality. As an exercise though, I find it interesting.)
I'm not sure if there is a better way in the general sense. Better ways would probably be app specific. Eager loading isn't a silver bullet, in some cases it's helpful to manage the loading manually using your own query, as you suggest. One alternative to the current approach would be attempting to use a subselect instead of a list of ids, which may be faster in some cases and slower in others. Really, if it's not a problem and not likely to become a problem, I wouldn't worry about it. Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
