https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292010-06-22T21:01:11ZRedmineRedmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=176282010-06-22T21:01:11ZYuki Kita
<ul></ul><p>This seems to be a duplicate of <a class="issue tracker-2 status-5 priority-3 priority-lowest closed" title="Feature: Being able to sort the issue list by custom fields (Closed)" href="https://www.redmine.org/issues/1139">#1139</a> / <a class="issue tracker-2 status-1 priority-4 priority-default" title="Feature: Is it possible to set the Custom Field of the project as used in filter, Searchable and used in s... (New)" href="https://www.redmine.org/issues/3488">#3488</a>.<br />You can do it if you check "Used as a filter" of the custom field and select it in "Administration/Settings/Issue tracking".</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=176292010-06-22T21:21:45ZCyber Sprocket
<ul></ul><p>This does not help. Tried that.</p>
<p>I still cannot sort by the rank field. Also tried putting a value in that field for all open tickets.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180112010-07-06T20:28:49ZCyber Sprocket
<ul></ul><p>Still happens in Redmine 0.9.5.stable.3824 (MySQL).</p>
<p>This is definitely related to sort in custom filters. As soon as I remove the rank (integer) from the sort the issue filter works fine.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180162010-07-06T21:18:40ZYuki Kita
<ul></ul><p>I post the patch to fix <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Defect in the filters (custom field) from the list of issue (Closed)" href="https://www.redmine.org/issues/5778">#5778</a>, and I assume it also fixes this issue.<br />Cyber,<br />can you apply the patch (attatched in <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Defect in the filters (custom field) from the list of issue (Closed)" href="https://www.redmine.org/issues/5778">#5778</a>) and report the result?</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180442010-07-08T01:21:16ZCyber Sprocket
<ul></ul><p>Yuki - Is there a quick way to apply patches, or do I have to hand-edit the code?</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180472010-07-08T02:56:34ZYuki Kita
<ul></ul><p>Cyber Sprocket wrote:</p>
<blockquote>
<p>Yuki - Is there a quick way to apply patches, or do I have to hand-edit the code?</p>
</blockquote>
<p>Here is how to apply the patch.</p>
<pre>
$ cd your-redmine-root
$ patch -p0 < custom_field_filter.patch
patching file app/models/query.rb
patching file app/views/queries/_filters.rhtml
</pre> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180502010-07-08T04:53:25ZCyber Sprocket
<ul></ul><p>It doesn't crash now, but it does not return results, the table is now blank.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180512010-07-08T04:56:31ZCyber Sprocket
<ul></ul><p>BTW, this happens even without custom queries. I changed the settings to show the Rank field on the default issue output. It shows up as the last column with the proper data intact. As soon as I click on the column heading to sort, the page returns but shows up blank.</p>
<p>You can see the effect (with the new patch in place) by going to our public portion of the site:<br /><a class="external" href="http://redmine.cybersprocket.com/projects/wpcafepress/issues">http://redmine.cybersprocket.com/projects/wpcafepress/issues</a></p>
<p>Click on "Rank" on the far right & it will now return the page "wireframe" but no issues table results:<br /><a class="external" href="http://redmine.cybersprocket.com/projects/wpcafepress/issues?sort=cf_4%2Cid%3Adesc">http://redmine.cybersprocket.com/projects/wpcafepress/issues?sort=cf_4%2Cid%3Adesc</a></p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180522010-07-08T05:26:19ZCyber Sprocket
<ul></ul><p>I found the problem:</p>
<p>custom_field.rb uses a cast to decimal. This is not supported in MySQL v4.1 which is what we are running on our server. MySQL v4.1 is stated as the minimum supported version in the install docs:<br /><a class="external" href="http://www.redmine.org/wiki/redmine/RedmineInstall">http://www.redmine.org/wiki/redmine/RedmineInstall</a></p>
<a name="custom_fieldrb"></a>
<h2 >custom_field.rb<a href="#custom_fieldrb" class="wiki-anchor">¶</a></h2>
<pre><code class="ruby syntaxhl"> <span class="k">when</span> <span class="s1">'int'</span><span class="p">,</span> <span class="s1">'float'</span>
<span class="c1"># Make the database cast values into numeric</span>
<span class="c1"># Postgresql will raise an error if a value can not be casted!</span>
<span class="c1"># CustomValue validations should ensure that it doesn't occur</span>
<span class="s2">"(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM </span><span class="si">#{</span><span class="no">CustomValue</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2"> cv_sort"</span> <span class="o">+</span>
<span class="s2">" WHERE cv_sort.customized_type='</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">name</span><span class="si">}</span><span class="s2">'"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.customized_id=</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2">.id"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.custom_field_id=</span><span class="si">#{</span><span class="nb">id</span><span class="si">}</span><span class="s2"> AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)"</span>
</code><br /></pre> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180532010-07-08T05:39:33ZCyber Sprocket
<ul></ul><p>This fixes the problem for us... but you still have an issue of what to do with FLOAT on MySQL 4.1 since cast to decimal is not an option.</p>
<pre>
<code class="ruby syntaxhl"> <span class="c1"># Returns a ORDER BY clause that can used to sort customized</span>
<span class="c1"># objects by their value of the custom field.</span>
<span class="c1"># Returns false, if the custom field can not be used for sorting.</span>
<span class="k">def</span> <span class="nf">order_statement</span>
<span class="k">case</span> <span class="n">field_format</span>
<span class="k">when</span> <span class="s1">'string'</span><span class="p">,</span> <span class="s1">'text'</span><span class="p">,</span> <span class="s1">'list'</span><span class="p">,</span> <span class="s1">'date'</span><span class="p">,</span> <span class="s1">'bool'</span>
<span class="c1"># COALESCE is here to make sure that blank and NULL values are sorted equally</span>
<span class="s2">"COALESCE((SELECT cv_sort.value FROM </span><span class="si">#{</span><span class="no">CustomValue</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2"> cv_sort"</span> <span class="o">+</span>
<span class="s2">" WHERE cv_sort.customized_type='</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">name</span><span class="si">}</span><span class="s2">'"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.customized_id=</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2">.id"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.custom_field_id=</span><span class="si">#{</span><span class="nb">id</span><span class="si">}</span><span class="s2"> LIMIT 1), '')"</span>
<span class="k">when</span> <span class="s1">'int'</span>
<span class="c1"># CyberSprocket.com fix...</span>
<span class="c1"># Make the database cast values into numeric</span>
<span class="c1"># Postgresql will raise an error if a value can not be casted!</span>
<span class="c1"># CustomValue validations should ensure that it doesn't occur</span>
<span class="s2">"(SELECT CAST(cv_sort.value AS signed) FROM </span><span class="si">#{</span><span class="no">CustomValue</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2"> cv_sort"</span> <span class="o">+</span>
<span class="s2">" WHERE cv_sort.customized_type='</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">name</span><span class="si">}</span><span class="s2">'"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.customized_id=</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2">.id"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.custom_field_id=</span><span class="si">#{</span><span class="nb">id</span><span class="si">}</span><span class="s2"> AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)"</span>
<span class="k">when</span> <span class="s1">'float'</span>
<span class="c1"># Make the database cast values into numeric</span>
<span class="c1"># Postgresql will raise an error if a value can not be casted!</span>
<span class="c1"># CustomValue validations should ensure that it doesn't occur</span>
<span class="s2">"(SELECT CAST(cv_sort.value AS decimal(60,3)) FROM </span><span class="si">#{</span><span class="no">CustomValue</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2"> cv_sort"</span> <span class="o">+</span>
<span class="s2">" WHERE cv_sort.customized_type='</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">name</span><span class="si">}</span><span class="s2">'"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.customized_id=</span><span class="si">#{</span><span class="nb">self</span><span class="p">.</span><span class="nf">class</span><span class="p">.</span><span class="nf">customized_class</span><span class="p">.</span><span class="nf">table_name</span><span class="si">}</span><span class="s2">.id"</span> <span class="o">+</span>
<span class="s2">" AND cv_sort.custom_field_id=</span><span class="si">#{</span><span class="nb">id</span><span class="si">}</span><span class="s2"> AND cv_sort.value <> '' AND cv_sort.value IS NOT NULL LIMIT 1)"</span>
<span class="k">else</span>
<span class="kp">nil</span>
<span class="k">end</span>
<span class="k">end</span>
</code><br /></pre> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180802010-07-08T19:28:09ZMischa The Evil
<ul></ul><p>Cyber Sprocket wrote:</p>
<blockquote>
<p>[...]</p>
<p>MySQL v4.1 is stated as the minimum supported version in the install docs:<br /><a class="external" href="http://www.redmine.org/wiki/redmine/RedmineInstall">http://www.redmine.org/wiki/redmine/RedmineInstall</a></p>
</blockquote>
<p>That info is <em>not entirely</em> true. See e.g. issue <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Failing test after r2257 with MySQL 4.1.20: "test_sort_by_float_custom_field_asc(QueryTest)" (Closed)" href="https://www.redmine.org/issues/2755">#2755</a>. Since Redmine <a class="version" href="https://www.redmine.org/versions/6">0.9.0</a> MySQL 5 is required to fully be compatible with the Redmine Core.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180942010-07-08T20:46:01ZCyber Sprocket
<ul></ul><p>Ok - well I guess that needs to be updated on the RedmineInstall page & a more prominent notice should go out to people when they upgrade.</p>
<p>We started on 0.8X and just went through the normal upgrade process. Never did id complain nor warn about requiring MySQL 5.x. Maybe even put a "we recommend MySQL 5.X, you are on 4.X" on the admin/info screen.</p>
<p>Also, I think the patch I proposed should be considered. At the very least it gets INT custom fields working on MySQL 4 installs and I can't see how it would hurt anyone on MySQL 5.X.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180972010-07-09T03:41:27ZYuki Kita
<ul><li><strong>Assignee</strong> set to <i>Muntek Singh</i></li></ul><blockquote>
<p>Ok - well I guess that needs to be updated on the RedmineInstall page & a more prominent notice should go out to people when they upgrade.</p>
</blockquote>
<p>Muntek,<br />Can you update <a class="wiki-page" href="https://www.redmine.org/projects/redmine/wiki/RedmineInstall">RedmineInstall</a> page so that users understand current Redmine does not support MySQL4.1?</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=180982010-07-09T03:55:59ZMuntek Singh
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>Done</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=182152010-07-11T17:21:50ZJean-Baptiste Barth
<ul></ul><p>JPL said he didn't want to drop Mysql 4.1 support in <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Failing test after r2257 with MySQL 4.1.20: "test_sort_by_float_custom_field_asc(QueryTest)" (Closed)" href="https://www.redmine.org/issues/2755">#2755</a> 1 year ago, I don't know if it's still the case. I personally think people should upgrade now, Mysql 4 is far too old and not supported at all since 12/2009.</p> Redmine - Defect #5726: Cannot sort issues on custom fieldshttps://www.redmine.org/issues/5726?journal_id=182172010-07-11T17:28:17ZCyber Sprocket
<ul></ul><p>The patch provided will at least allow int support on custom fields on 4.X.</p>
<p>As for the MySQL upgrade to 5.X, that is great in theory however many sites including our own makes that a big undertaking. We have a half-dozen clients on the server with some using systems built on MySQL 4.x.</p>
<p>Side-by-side installs of MySQL can be problematic. Upgrading all of our clients will cost us time, which for us is money. Our clients won't pay for the upgrade. To them it is not broken (their apps work) so why fix it. The simple argument of "MySQL 4 is too old" sounds good on the surface, but out in the real world the issue is not so cut & dried. We too agree that people should be on supported software like MySQL 5 and PHP5, but alas that is not something we can force on our clients... and in our business our clients are our livelihood.</p>