Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is really pretty hairy, and any solution isn't going to be pretty, but it <em>does</em> look to be possible if you bend the rules a little bit. Forgive any mistakes I make, as I didn't go and create a schema to test this on, but it's based on the best info I have (and much help from ribasushi).</p> <p>First, (assuming that your userfields table has a belongs_to relation with the customfields table, called <code>customfield</code>)</p> <pre><code>my $mbd = $userfields_rs-&gt;search( { 'customfield.name' =&gt; 'Mothers birthdate', 'uf.uid' =&gt; \'me.uid' # reference to outer query }, { join =&gt; 'customfield', alias =&gt; 'uf', # don't shadow the 'me' alias here. } )-&gt;get_column('content')-&gt;as_query; # Subqueries and -as don't currently mix, so hack the SQL ourselves $mbd-&gt;[0] .= q{ AS 'Mothers Birthdate'}; </code></pre> <p>The literal <code>me.uid</code> that uf.uid is being matched against is an unbound variable -- it's the <code>uid</code> field from the query that we're eventually going to put this query into as a subselect. By default DBIC aliases the table that the query is addressing to <code>me</code>; if you gave it a different alias then you would use something diferent here. Anyway, You could repeat this <code>as_query</code> business with as many different fields as you like, just varying the field-name (if you're smart, you'll write a method to generate them), and put them in an array, so now let's suppose that <code>@field_queries</code> is an array, containing <code>$mbd</code> above as well as another one based on Join Date, and anything you like.</p> <p>Once you have that, it's as "simple" as...</p> <pre><code>my $users = $useraccounts_rs-&gt;search( { }, # any search criteria can go in here, { '+select' =&gt; [ @field_queries ], '+as' =&gt; [qw/mothers_birthdate join_date/], # this is not SQL AS order_by =&gt; {-asc =&gt; 'Mothers birthdate'}, } ); </code></pre> <p>which will include each of the subqueries into the select. </p> <p>Now for the sad part: as of right now, this whole thing actually <em>won't</em> work, because subqueries with placeholders don't work properly. So for now you need an additional workaround: instead of <code>'customfield.name' =&gt; 'Mothers birthdate'</code> in the subselect search, do <code>'customfield.name' =&gt; \q{'Mothers birthdate'}</code> -- this is using literal SQL for the field name (<strong>BE CAREFUL</strong> of SQL injection here!), which will sidestep the placeholder bug. But in the not-too-distant future, that bug will be resolved and the code above will work okay, and we'll update the answer to let you know that's the case.</p> <p>See DBIx::Class::ResultSource <a href="https://metacpan.org/pod/release/RIBASUSHI/DBIx-Class-0.082841/lib/DBIx/Class/ResultSet.pm#order_by" rel="nofollow noreferrer">order_by documentation</a> </p>
    singulars
    1. This table or related slice is empty.
    1. POHow do I do this search and order_by on a DBIx::Class::ResultSet
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USpalik
    1. UShobbs
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do I do this search and order_by on a DBIx::Class::ResultSet
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks so much Hobbs (and Ribasushi) :). That's more help than I could have hoped for in such a short space of time :D. Much kudos to you :)
      singulars
      1. PO
      1. USPaul Hutchinson
    2. COTwo points to this. 1) ->get_column('content')->as_query; returns a reference to a reference of an array not the expected reference to an array, this may well be a bug in my current dbix::class but I couldn't see any fixes mentioned in the changelog and the documentation seems to suggest this is expected behavior! I solved this by doing ${$userfields_rs->search(...)} 2) I solved the placeholder issue by adding 'bind' => $mbd->[1] to my ->search. After much playing this morning it all works :)
      singulars
      1. PO
      1. USPaul Hutchinson
    3. COslight problem if I call ->paging on the resultset, I can't get ->total_entries out of it, looks like it grabs the placeholders that it doesn't need and appends them to the end, which causes an err. for now getting around that with an eval, but it's ugly.
      singulars
      1. PO
      1. USPaul Hutchinson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload