snipt

Ctrl+h for KB shortcuts

MySQL

Two SQL Queries to get url paths from nids - one optimised

SELECT n.nid, n.vid, n.type, n.title, n.status, url.src, url.dst FROM node n
LEFT JOIN url_alias url ON concat('node/', n.nid) = url.src
WHERE n.type LIKE '%_profile'
AND url.dst NOT LIKE 'content/%'
LIMIT 0, 200


Updated better version (1000 x faster):


SELECT n.nid, n.vid, n.type, n.title, n.status, url.src, url.dst
FROM url_alias url
INNER JOIN (
  SELECT concat( 'node/', n.nid ) AS path, n.nid, n.vid, n.type, n.title, n.status
  FROM node n
  WHERE n.type = 'contact_profile'
) n ON url.src = n.path
https://snipt.net/embed/d98d93612528f2dcc0b8c0e06e4beb12/
/raw/d98d93612528f2dcc0b8c0e06e4beb12/
d98d93612528f2dcc0b8c0e06e4beb12
mysql
MySQL
17
2019-07-22T14:13:31
True
False
False
/api/public/snipt/34192/
sql-to-get-url-paths-from-nids
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">nid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">vid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">type</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">title</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">status</span><span class="p">,</span> <span class="n">url</span><span class="p">.</span><span class="n">src</span><span class="p">,</span> <span class="n">url</span><span class="p">.</span><span class="n">dst</span> <span class="k">FROM</span> <span class="n">node</span> <span class="n">n</span> </span><span id="L-2"><a name="L-2"></a><span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">url_alias</span> <span class="n">url</span> <span class="k">ON</span> <span class="nf">concat</span><span class="p">(</span><span class="s1">&#39;node/&#39;</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">nid</span><span class="p">)</span> <span class="o">=</span> <span class="n">url</span><span class="p">.</span><span class="n">src</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">n</span><span class="p">.</span><span class="n">type</span> <span class="k">LIKE</span> <span class="s1">&#39;%_profile&#39;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">AND</span> <span class="n">url</span><span class="p">.</span><span class="n">dst</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">&#39;content/%&#39;</span> </span><span id="L-5"><a name="L-5"></a><span class="k">LIMIT</span> <span class="mi">0</span><span class="p">,</span> <span class="mi">200</span> </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a> </span><span id="L-8"><a name="L-8"></a><span class="n">Updated</span> <span class="n">better</span> <span class="nf">version</span> <span class="p">(</span><span class="mi">1000</span> <span class="n">x</span> <span class="n">faster</span><span class="p">):</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a> </span><span id="L-11"><a name="L-11"></a><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">nid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">vid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">type</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">title</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">status</span><span class="p">,</span> <span class="n">url</span><span class="p">.</span><span class="n">src</span><span class="p">,</span> <span class="n">url</span><span class="p">.</span><span class="n">dst</span> </span><span id="L-12"><a name="L-12"></a><span class="k">FROM</span> <span class="n">url_alias</span> <span class="n">url</span> </span><span id="L-13"><a name="L-13"></a><span class="k">INNER</span> <span class="k">JOIN</span> <span class="p">(</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">SELECT</span> <span class="nf">concat</span><span class="p">(</span> <span class="s1">&#39;node/&#39;</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">nid</span> <span class="p">)</span> <span class="k">AS</span> <span class="n">path</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">nid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">vid</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">type</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">title</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">status</span> </span><span id="L-15"><a name="L-15"></a> <span class="k">FROM</span> <span class="n">node</span> <span class="n">n</span> </span><span id="L-16"><a name="L-16"></a> <span class="k">WHERE</span> <span class="n">n</span><span class="p">.</span><span class="n">type</span> <span class="o">=</span> <span class="s1">&#39;contact_profile&#39;</span> </span><span id="L-17"><a name="L-17"></a><span class="p">)</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">url</span><span class="p">.</span><span class="n">src</span> <span class="o">=</span> <span class="n">n</span><span class="p">.</span><span class="n">path</span> </span></pre></div> </td></tr></table>
drupal, mysql, sql