Maybe if you were not so eager to shorten the code and forgot you can "rename" a table for the query, and if you used JOIN: 

SELECT words.id, words.definition
  FROM words 
  JOIN types ON words.typeID = types.id
  JOIN stockmatch ON words.id = stockmatch.wordID
  JOIN stock ON stockmatch.stockID = stock.id 
  JOIN languages ON stock.languageID = languages.id
WHERE stock.word = ? 
  AND languages.name = ? 
  AND types.name = ?

[download]
or 
SELECT words.id, words.definition
  FROM words 
  JOIN types ON words.typeID = types.id AND types.name = ?
  JOIN stockmatch ON words.id = stockmatch.wordID
  JOIN stock ON stockmatch.stockID = stock.id AND stock.word = ?
  JOIN languages ON stock.languageID = languages.id AND languages.name
+ = ?

[download]
(Now, maybe I'm making a fool of myself. I've worked with Oracle and MS SQL, never with mysql, so I don't know it it allows this syntax.)

On the other hand ... once one would get used to your "language" he might be able to put the query together quicker than in SQL. (Even if only thanks to the fact it's fewer characters). But what if I wanted to use some "advanced" feature of the SQL server? Any Column in (SELECT Column FROM Table WHERE ...) or  WHERE exists (SELECT * FROM Table WHERE ...) or ...

If I stay with SQL then it's much easier to start using those.

Also (again possibly nonsense, does mysql allow stored procedures?) it's generaly considered better to put especialy the complex queries into stored procedures. That way you do not force the server to recompile the query and regenerate the execution plan every time. Yes $dbh->prepare() helps, but still isn't perfect. Each process has to submit the query for compilation at least once.

Jenda

In reply to Re: Re: Re: Module Design Review -- DB mini language by Jenda
in thread Module Design Review -- DB mini language by elusion

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.