QGIS API Documentation  2.99.0-Master (f1c3692)
qgssearchquerybuilder.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgssearchquerybuilder.cpp - Query builder for search strings
3  ----------------------
4  begin : March 2006
5  copyright : (C) 2006 by Martin Dobias
6  email : wonder.sk at gmail dot com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 
16 #include <QDomDocument>
17 #include <QDomElement>
18 #include <QFileDialog>
19 #include <QFileInfo>
20 #include <QInputDialog>
21 #include <QListView>
22 #include <QMessageBox>
23 #include <QStandardItem>
24 #include <QTextStream>
25 
26 #include "qgssettings.h"
27 #include "qgsfeature.h"
28 #include "qgsfeatureiterator.h"
29 #include "qgsfields.h"
30 #include "qgssearchquerybuilder.h"
31 #include "qgsexpression.h"
32 #include "qgsvectorlayer.h"
33 #include "qgslogger.h"
34 #include "qgshelp.h"
35 
37  QWidget *parent, Qt::WindowFlags fl )
38  : QDialog( parent, fl )
39  , mLayer( layer )
40 {
41  setupUi( this );
42  connect( btnEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnEqual_clicked );
43  connect( btnLessThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessThan_clicked );
44  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterThan_clicked );
45  connect( btnLike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLike_clicked );
46  connect( btnILike, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnILike_clicked );
47  connect( btnPct, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnPct_clicked );
48  connect( btnIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnIn_clicked );
49  connect( btnNotIn, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotIn_clicked );
50  connect( lstFields, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstFields_doubleClicked );
51  connect( lstValues, &QListView::doubleClicked, this, &QgsSearchQueryBuilder::lstValues_doubleClicked );
52  connect( btnLessEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnLessEqual_clicked );
53  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGreaterEqual_clicked );
54  connect( btnNotEqual, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNotEqual_clicked );
55  connect( btnAnd, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnAnd_clicked );
56  connect( btnNot, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnNot_clicked );
57  connect( btnOr, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnOr_clicked );
58  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnGetAllValues_clicked );
59  connect( btnSampleValues, &QPushButton::clicked, this, &QgsSearchQueryBuilder::btnSampleValues_clicked );
60  setupListViews();
61  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsSearchQueryBuilder::showHelp );
62 
63  setWindowTitle( tr( "Search Query Builder" ) );
64 
65  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
66  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
67  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnTest_clicked );
68 
69  pbn = new QPushButton( tr( "&Clear" ) );
70  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
71  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::btnClear_clicked );
72 
73  pbn = new QPushButton( tr( "&Save..." ) );
74  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
75  pbn->setToolTip( tr( "Save query to an xml file" ) );
76  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::saveQuery );
77 
78  pbn = new QPushButton( tr( "&Load..." ) );
79  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
80  pbn->setToolTip( tr( "Load query from xml file" ) );
81  connect( pbn, &QAbstractButton::clicked, this, &QgsSearchQueryBuilder::loadQuery );
82 
83  if ( layer )
84  lblDataUri->setText( layer->name() );
85  populateFields();
86 }
87 
88 void QgsSearchQueryBuilder::populateFields()
89 {
90  if ( !mLayer )
91  return;
92 
93  const QgsFields &fields = mLayer->fields();
94  for ( int idx = 0; idx < fields.count(); ++idx )
95  {
96  QString fieldName = fields.at( idx ).name();
97  mFieldMap[fieldName] = idx;
98  QStandardItem *myItem = new QStandardItem( fieldName );
99  myItem->setEditable( false );
100  mModelFields->insertRow( mModelFields->rowCount(), myItem );
101  }
102 }
103 
104 void QgsSearchQueryBuilder::setupListViews()
105 {
106  //Models
107  mModelFields = new QStandardItemModel();
108  mModelValues = new QStandardItemModel();
109  lstFields->setModel( mModelFields );
110  lstValues->setModel( mModelValues );
111  // Modes
112  lstFields->setViewMode( QListView::ListMode );
113  lstValues->setViewMode( QListView::ListMode );
114  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
115  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
116  // Performance tip since Qt 4.1
117  lstFields->setUniformItemSizes( true );
118  lstValues->setUniformItemSizes( true );
119 }
120 
121 void QgsSearchQueryBuilder::getFieldValues( int limit )
122 {
123  if ( !mLayer )
124  {
125  return;
126  }
127  // clear the values list
128  mModelValues->clear();
129 
130  // determine the field type
131  QString fieldName = mModelFields->data( lstFields->currentIndex() ).toString();
132  int fieldIndex = mFieldMap[fieldName];
133  QgsField field = mLayer->fields().at( fieldIndex );//provider->fields().at( fieldIndex );
134  bool numeric = ( field.type() == QVariant::Int || field.type() == QVariant::Double );
135 
136  QgsFeature feat;
137  QString value;
138 
139  QgsAttributeList attrs;
140  attrs.append( fieldIndex );
141 
142  QgsFeatureIterator fit = mLayer->getFeatures( QgsFeatureRequest().setFlags( QgsFeatureRequest::NoGeometry ).setSubsetOfAttributes( attrs ) );
143 
144  lstValues->setCursor( Qt::WaitCursor );
145  // Block for better performance
146  mModelValues->blockSignals( true );
147  lstValues->setUpdatesEnabled( false );
148 
149  // MH: keep already inserted values in a set. Querying is much faster compared to QStandardItemModel::findItems
150  QSet<QString> insertedValues;
151 
152  while ( fit.nextFeature( feat ) &&
153  ( limit == 0 || mModelValues->rowCount() != limit ) )
154  {
155  value = feat.attribute( fieldIndex ).toString();
156 
157  if ( !numeric )
158  {
159  // put string in single quotes and escape single quotes in the string
160  value = '\'' + value.replace( '\'', QLatin1String( "''" ) ) + '\'';
161  }
162 
163  // add item only if it's not there already
164  if ( !insertedValues.contains( value ) )
165  {
166  QStandardItem *myItem = new QStandardItem( value );
167  myItem->setEditable( false );
168  mModelValues->insertRow( mModelValues->rowCount(), myItem );
169  insertedValues.insert( value );
170  }
171  }
172  // Unblock for normal use
173  mModelValues->blockSignals( false );
174  lstValues->setUpdatesEnabled( true );
175  // TODO: already sorted, signal emit to refresh model
176  mModelValues->sort( 0 );
177  lstValues->setCursor( Qt::ArrowCursor );
178 }
179 
180 void QgsSearchQueryBuilder::btnSampleValues_clicked()
181 {
182  getFieldValues( 25 );
183 }
184 
185 void QgsSearchQueryBuilder::btnGetAllValues_clicked()
186 {
187  getFieldValues( 0 );
188 }
189 
190 void QgsSearchQueryBuilder::btnTest_clicked()
191 {
192  long count = countRecords( txtSQL->text() );
193 
194  // error?
195  if ( count == -1 )
196  return;
197 
198  QMessageBox::information( this, tr( "Search results" ), tr( "Found %n matching feature(s).", "test result", count ) );
199 }
200 
201 // This method tests the number of records that would be returned
202 long QgsSearchQueryBuilder::countRecords( const QString &searchString )
203 {
204  QgsExpression search( searchString );
205  if ( search.hasParserError() )
206  {
207  QMessageBox::critical( this, tr( "Search string parsing error" ), search.parserErrorString() );
208  return -1;
209  }
210 
211  if ( !mLayer )
212  return -1;
213 
214  bool fetchGeom = search.needsGeometry();
215 
216  int count = 0;
217  QgsFeature feat;
218 
220 
221  if ( !search.prepare( &context ) )
222  {
223  QMessageBox::critical( this, tr( "Evaluation error" ), search.evalErrorString() );
224  return -1;
225  }
226 
227  QApplication::setOverrideCursor( Qt::WaitCursor );
228 
230 
231  while ( fit.nextFeature( feat ) )
232  {
233  context.setFeature( feat );
234  QVariant value = search.evaluate( &context );
235  if ( value.toInt() != 0 )
236  {
237  count++;
238  }
239 
240  // check if there were errors during evaulating
241  if ( search.hasEvalError() )
242  break;
243  }
244 
245  QApplication::restoreOverrideCursor();
246 
247  if ( search.hasEvalError() )
248  {
249  QMessageBox::critical( this, tr( "Error during search" ), search.evalErrorString() );
250  return -1;
251  }
252 
253  return count;
254 }
255 
256 
257 void QgsSearchQueryBuilder::on_btnOk_clicked()
258 {
259  // if user hits OK and there is no query, skip the validation
260  if ( txtSQL->text().trimmed().length() > 0 )
261  {
262  accept();
263  return;
264  }
265 
266  // test the query to see if it will result in a valid layer
267  long numRecs = countRecords( txtSQL->text() );
268  if ( numRecs == -1 )
269  {
270  // error shown in countRecords
271  }
272  else if ( numRecs == 0 )
273  {
274  QMessageBox::warning( this, tr( "No Records" ), tr( "The query you specified results in zero records being returned." ) );
275  }
276  else
277  {
278  accept();
279  }
280 
281 }
282 
283 void QgsSearchQueryBuilder::btnEqual_clicked()
284 {
285  txtSQL->insertText( QStringLiteral( " = " ) );
286 }
287 
288 void QgsSearchQueryBuilder::btnLessThan_clicked()
289 {
290  txtSQL->insertText( QStringLiteral( " < " ) );
291 }
292 
293 void QgsSearchQueryBuilder::btnGreaterThan_clicked()
294 {
295  txtSQL->insertText( QStringLiteral( " > " ) );
296 }
297 
298 void QgsSearchQueryBuilder::btnPct_clicked()
299 {
300  txtSQL->insertText( QStringLiteral( "%" ) );
301 }
302 
303 void QgsSearchQueryBuilder::btnIn_clicked()
304 {
305  txtSQL->insertText( QStringLiteral( " IN " ) );
306 }
307 
308 void QgsSearchQueryBuilder::btnNotIn_clicked()
309 {
310  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
311 }
312 
313 void QgsSearchQueryBuilder::btnLike_clicked()
314 {
315  txtSQL->insertText( QStringLiteral( " LIKE " ) );
316 }
317 
319 {
320  return txtSQL->text();
321 }
322 
324 {
325  txtSQL->setText( searchString );
326 }
327 
328 void QgsSearchQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
329 {
330  txtSQL->insertText( QgsExpression::quotedColumnRef( mModelFields->data( index ).toString() ) );
331 }
332 
333 void QgsSearchQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
334 {
335  txtSQL->insertText( mModelValues->data( index ).toString() );
336 }
337 
338 void QgsSearchQueryBuilder::btnLessEqual_clicked()
339 {
340  txtSQL->insertText( QStringLiteral( " <= " ) );
341 }
342 
343 void QgsSearchQueryBuilder::btnGreaterEqual_clicked()
344 {
345  txtSQL->insertText( QStringLiteral( " >= " ) );
346 }
347 
348 void QgsSearchQueryBuilder::btnNotEqual_clicked()
349 {
350  txtSQL->insertText( QStringLiteral( " != " ) );
351 }
352 
353 void QgsSearchQueryBuilder::btnAnd_clicked()
354 {
355  txtSQL->insertText( QStringLiteral( " AND " ) );
356 }
357 
358 void QgsSearchQueryBuilder::btnNot_clicked()
359 {
360  txtSQL->insertText( QStringLiteral( " NOT " ) );
361 }
362 
363 void QgsSearchQueryBuilder::btnOr_clicked()
364 {
365  txtSQL->insertText( QStringLiteral( " OR " ) );
366 }
367 
368 void QgsSearchQueryBuilder::btnClear_clicked()
369 {
370  txtSQL->clear();
371 }
372 
373 void QgsSearchQueryBuilder::btnILike_clicked()
374 {
375  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
376 }
377 
379 {
380  QgsSettings s;
381  QString lastQueryFileDir = s.value( QStringLiteral( "/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
382  //save as qqt (QGIS query file)
383  QString saveFileName = QFileDialog::getSaveFileName( nullptr, tr( "Save query to file" ), lastQueryFileDir, QStringLiteral( "*.qqf" ) );
384  if ( saveFileName.isNull() )
385  {
386  return;
387  }
388 
389  if ( !saveFileName.endsWith( QLatin1String( ".qqf" ), Qt::CaseInsensitive ) )
390  {
391  saveFileName += QLatin1String( ".qqf" );
392  }
393 
394  QFile saveFile( saveFileName );
395  if ( !saveFile.open( QIODevice::WriteOnly | QIODevice::Truncate ) )
396  {
397  QMessageBox::critical( nullptr, tr( "Error" ), tr( "Could not open file for writing" ) );
398  return;
399  }
400 
401  QDomDocument xmlDoc;
402  QDomElement queryElem = xmlDoc.createElement( QStringLiteral( "Query" ) );
403  QDomText queryTextNode = xmlDoc.createTextNode( txtSQL->text() );
404  queryElem.appendChild( queryTextNode );
405  xmlDoc.appendChild( queryElem );
406 
407  QTextStream fileStream( &saveFile );
408  xmlDoc.save( fileStream, 2 );
409 
410  QFileInfo fi( saveFile );
411  s.setValue( QStringLiteral( "/UI/lastQueryFileDir" ), fi.absolutePath() );
412 }
413 
415 {
416  QgsSettings s;
417  QString lastQueryFileDir = s.value( QStringLiteral( "/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
418 
419  QString queryFileName = QFileDialog::getOpenFileName( nullptr, tr( "Load query from file" ), lastQueryFileDir, tr( "Query files" ) + " (*.qqf);;" + tr( "All files" ) + " (*)" );
420  if ( queryFileName.isNull() )
421  {
422  return;
423  }
424 
425  QFile queryFile( queryFileName );
426  if ( !queryFile.open( QIODevice::ReadOnly ) )
427  {
428  QMessageBox::critical( nullptr, tr( "Error" ), tr( "Could not open file for reading" ) );
429  return;
430  }
431  QDomDocument queryDoc;
432  if ( !queryDoc.setContent( &queryFile ) )
433  {
434  QMessageBox::critical( nullptr, tr( "Error" ), tr( "File is not a valid xml document" ) );
435  return;
436  }
437 
438  QDomElement queryElem = queryDoc.firstChildElement( QStringLiteral( "Query" ) );
439  if ( queryElem.isNull() )
440  {
441  QMessageBox::critical( nullptr, tr( "Error" ), tr( "File is not a valid query document" ) );
442  return;
443  }
444 
445  QString query = queryElem.text();
446 
447  //todo: test if all the attributes are valid
448  QgsExpression search( query );
449  if ( search.hasParserError() )
450  {
451  QMessageBox::critical( this, tr( "Search string parsing error" ), search.parserErrorString() );
452  return;
453  }
454 
455  QString newQueryText = query;
456 
457 #if 0
458  // TODO: implement with visitor pattern in QgsExpression
459 
460  QStringList attributes = searchTree->referencedColumns();
461  QMap< QString, QString> attributesToReplace;
462  QStringList existingAttributes;
463 
464  //get all existing fields
465  QMap<QString, int>::const_iterator fieldIt = mFieldMap.constBegin();
466  for ( ; fieldIt != mFieldMap.constEnd(); ++fieldIt )
467  {
468  existingAttributes.push_back( fieldIt.key() );
469  }
470 
471  //if a field does not exist, ask what field should be used instead
472  QStringList::const_iterator attIt = attributes.constBegin();
473  for ( ; attIt != attributes.constEnd(); ++attIt )
474  {
475  //test if attribute is there
476  if ( !mFieldMap.contains( attIt ) )
477  {
478  bool ok;
479  QString replaceAttribute = QInputDialog::getItem( 0, tr( "Select attribute" ), tr( "There is no attribute '%1' in the current vector layer. Please select an existing attribute" ).arg( *attIt ),
480  existingAttributes, 0, false, &ok );
481  if ( !ok || replaceAttribute.isEmpty() )
482  {
483  return;
484  }
485  attributesToReplace.insert( *attIt, replaceAttribute );
486  }
487  }
488 
489  //Now replace all the string in the query
490  QList<QgsSearchTreeNode *> columnRefList = searchTree->columnRefNodes();
491  QList<QgsSearchTreeNode *>::iterator columnIt = columnRefList.begin();
492  for ( ; columnIt != columnRefList.end(); ++columnIt )
493  {
494  QMap< QString, QString>::const_iterator replaceIt = attributesToReplace.find( ( *columnIt )->columnRef() );
495  if ( replaceIt != attributesToReplace.constEnd() )
496  {
497  ( *columnIt )->setColumnRef( replaceIt.value() );
498  }
499  }
500 
501  if ( attributesToReplace.size() > 0 )
502  {
503  newQueryText = query;
504  }
505 #endif
506 
507  txtSQL->clear();
508  txtSQL->insertText( newQueryText );
509 }
510 
511 void QgsSearchQueryBuilder::showHelp()
512 {
513  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
514 }
Wrapper for iterator of features from vector data provider or vector layer.
QString name
Definition: qgsfield.h:56
This class is a composition of two QSettings instances:
Definition: qgssettings.h:55
void setFeature(const QgsFeature &feature)
Convenience function for setting a feature for the context.
void setSearchString(const QString &searchString)
change search string shown in text field
Container of fields for a vector layer.
Definition: qgsfields.h:42
The feature class encapsulates a single feature including its id, geometry and a list of field/values...
Definition: qgsfeature.h:62
int count() const
Return number of items.
Definition: qgsfields.cpp:115
QgsField at(int i) const
Get field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:145
void setValue(const QString &key, const QVariant &value, const QgsSettings::Section section=QgsSettings::NoSection)
Sets the value of setting key to value.
QgsFields fields() const override
Returns the list of fields of this layer.
Expression contexts are used to encapsulate the parameters around which a QgsExpression should be eva...
This class wraps a request for features to a vector layer (or directly its vector data provider)...
static QList< QgsExpressionContextScope * > globalProjectLayerScopes(const QgsMapLayer *layer)
Creates a list of three scopes: global, layer&#39;s project and layer.
Encapsulate a field in an attribute table or data source.
Definition: qgsfield.h:48
QgsFeatureIterator getFeatures(const QgsFeatureRequest &request=QgsFeatureRequest()) const override
Query the layer for features specified in request.
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), const Section section=NoSection) const
Returns the value for setting key.
QgsSearchQueryBuilder(QgsVectorLayer *layer, QWidget *parent SIP_TRANSFERTHIS=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
Constructor - takes pointer to vector layer as a parameter.
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:34
QString name
Definition: qgsmaplayer.h:60
QList< int > QgsAttributeList
Definition: qgsfield.h:27
bool nextFeature(QgsFeature &f)
Geometry is not required. It may still be returned if e.g. required for a filter condition.
Represents a vector layer which manages a vector based data sets.
QVariant::Type type() const
Gets variant type of the field as it will be retrieved from data source.
Definition: qgsfield.cpp:93
QVariant attribute(const QString &name) const
Lookup attribute value from attribute name.
Definition: qgsfeature.cpp:255
QString searchString()
returns newly created search string